// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId3");
GenerateWorksheetPart1Content(worksheetPart1);
WorksheetPart worksheetPart2 = workbookPart1.AddNewPart<WorksheetPart>("rId2");
GenerateWorksheetPart2Content(worksheetPart2);
WorksheetPart worksheetPart3 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPart3Content(worksheetPart3);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId5");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId4");
GenerateThemePart1Content(themePart1);
SetPackageProperties(document);
}
public void Print(SpreadsheetDocument document)
{
var extendedFilePropertiesPart = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
generateExtendedFilePropertiesPartContent(extendedFilePropertiesPart);
var workbookPart = document.AddWorkbookPart();
generateWorkbookPartContent(workbookPart);
var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rId3");
GenerateWorkbookStylesPartContent(workbookStylesPart);
var themePart = workbookPart.AddNewPart<ThemePart>("rId2");
generateThemePartContent(themePart);
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPartContent(worksheetPart);
var spreadsheetPrinterSettingsPart = worksheetPart.AddNewPart<SpreadsheetPrinterSettingsPart>("rId1");
generateSpreadsheetPrinterSettingsPartContent(spreadsheetPrinterSettingsPart);
var sharedStringTablePart = workbookPart.AddNewPart<SharedStringTablePart>("rId4");
GenerateSharedStringTablePartContent(sharedStringTablePart);
setPackageProperties(document);
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId3");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId2");
GenerateThemePart1Content(themePart1);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPart1Content(worksheetPart1);
DrawingsPart drawingsPart1 = worksheetPart1.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart1Content(drawingsPart1);
ImagePart imagePart1 = drawingsPart1.AddNewPart<ImagePart>("image/jpeg", "rId1");
GenerateImagePart1Content(imagePart1);
SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1 = worksheetPart1.AddNewPart<SpreadsheetPrinterSettingsPart>("rId1");
GenerateSpreadsheetPrinterSettingsPart1Content(spreadsheetPrinterSettingsPart1);
CalculationChainPart calculationChainPart1 = workbookPart1.AddNewPart<CalculationChainPart>("rId5");
GenerateCalculationChainPart1Content(calculationChainPart1);
SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId4");
GenerateSharedStringTablePart1Content(sharedStringTablePart1);
SetPackageProperties(document);
}
private void CreateParts(SpreadsheetDocument document)
{
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
// Create sharedstring
SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId7");
GenerateSharedStringTablePart1Content(sharedStringTablePart1);
// Create Sheet2
WorksheetPart worksheetPart2 = workbookPart1.AddNewPart<WorksheetPart>("rId2");
GenerateWorksheetPart2Content(worksheetPart2);
// Create Sheet1
WorksheetPart worksheetPart3 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPart3Content(worksheetPart3);
// Add PivotTablePart to Sheet1
PivotTablePart pivotTablePart1 = worksheetPart3.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart1Content(pivotTablePart1);
// Add PivotTableCacheDefinitionPart to PivotTablePart
PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 = pivotTablePart1.AddNewPart<PivotTableCacheDefinitionPart>("rId1");
GeneratePivotTableCacheDefinitionPart1Content(pivotTableCacheDefinitionPart1);
// Add PivotTableCacheDefinitionPart to PivotTableCacheDefinitionPart
PivotTableCacheRecordsPart pivotTableCacheRecordsPart1 = pivotTableCacheDefinitionPart1.AddNewPart<PivotTableCacheRecordsPart>("rId1");
GeneratePivotTableCacheRecordsPart1Content(pivotTableCacheRecordsPart1);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId6");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
workbookPart1.AddPart(pivotTableCacheDefinitionPart1, "rId4");
}
public ExcelHelper Create(string ExcelFile)
{
System.IO.FileInfo fi = new System.IO.FileInfo(ExcelFile);
if (!fi.Directory.Exists)
fi.Directory.Create();
_doc = SpreadsheetDocument.Create(ExcelFile, SpreadsheetDocumentType.Workbook);
_wbPart = _doc.AddWorkbookPart();
_wbPart.Workbook = new Workbook();
_doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
return _instance;
}
// Adds child parts and generates content of the specified part
private static void CreateParts(SpreadsheetDocument document)
{
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
Worksheet worksheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
worksheet1.Append(sheetData1);
worksheetPart1.Worksheet = worksheet1;
//GenerateWorksheetPart1Content(worksheetPart1);
}
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();
//}
}
}
/// <summary>
/// Copy sheet to another document
/// </summary>
/// <param name="sourceDoc">Source document</param>
/// <param name="srcSheetName">Name of source sheet</param>
/// <param name="targetDoc">Spreadsheet document to copied</param>
/// <param name="targetIndex">Index of copied sheet in target document</param>
public static void CopyWorksheet(SpreadsheetDocument sourceDoc, String srcSheetName, SpreadsheetDocument targetDoc, uint targetIndex)
{
// Locate the source sheet
if (sourceDoc.WorkbookPart == null)
throw new InvalidOperationException("WorkbookPart is not exist in sourceDoc!");
if (sourceDoc.WorkbookPart.Workbook.Sheets == null)
throw new InvalidOperationException("No sheets exist in sourceDoc!");
var srcSheet =
sourceDoc.WorkbookPart.Workbook.Sheets.Descendants<Sheet>()
.FirstOrDefault(a => a.Name == srcSheetName);
if (srcSheet == null)
throw new InvalidOperationException(String.Format("No sheet found with name {0}!", srcSheetName));
var srcSheetPart = sourceDoc.WorkbookPart.GetPartById(srcSheet.Id) as WorksheetPart;
if (srcSheetPart == null)
throw new InvalidOperationException(String.Format("Cannot find worksheet part with Id {0}!", srcSheet.Id));
var srcWorkSheet = srcSheetPart.Worksheet;
if (srcWorkSheet == null)
throw new InvalidOperationException("Worksheet not exist in source worksheet part!");
// Locate the position of target sheet
WorkbookPart tgtWbPart = targetDoc.WorkbookPart ?? targetDoc.AddWorkbookPart();
Sheets tgtSheets = tgtWbPart.Workbook.Sheets ?? tgtWbPart.Workbook.AppendChild<Sheets>(new Sheets());
if (targetIndex > tgtSheets.Count()) targetIndex = (uint)tgtSheets.Count();
// Create a new worksheet and clone data from original worksheet
var newSheetPart = tgtWbPart.AddNewPart<WorksheetPart>();
newSheetPart.Worksheet = new Worksheet(); //srcWorkSheet.Clone() as Worksheet;
// Create a unique ID for the new worksheet.
uint sheetId = 1;
if (tgtSheets.Elements<Sheet>().Any())
{
sheetId = tgtSheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Add cloned worksheet to target workbook
var newSheet = new Sheet()
{
Id = tgtWbPart.GetIdOfPart(newSheetPart),
SheetId = sheetId,
Name = srcSheet.Name
};
tgtSheets.InsertAt(newSheet, (int)targetIndex);
// Import data from source sheet to target sheet
ImportWorksheet(sourceDoc, srcWorkSheet, targetDoc, newSheetPart.Worksheet);
// Import all necessary resources into target document that referenced by cloned sheet
//ImportResources(sourceDoc, newSheetPart.Worksheet, targetDoc);
// Import defined names
ImportDefinedNames(sourceDoc, srcSheetName, targetDoc);
// Import calculate chain
ImportCalculateChain(sourceDoc, (int)srcSheet.SheetId.Value, targetDoc, (int)sheetId);
// Save it
tgtWbPart.Workbook.Save();
}
public override void Save(ResearchResult result)
{
if (!Directory.Exists(storageStr))
{
Directory.CreateDirectory(storageStr);
}
string fileName = storageStr + result.ResearchName;
if (File.Exists(fileName + ".xls") || File.Exists(fileName + ".xlsx"))
fileName += result.ResearchID;
document = SpreadsheetDocument.Create(fileName + ".xlsx", SpreadsheetDocumentType.Workbook);
workbook = document.AddWorkbookPart();
workbook.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbook.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = document.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
/*SaveResearchInfo(result.ResearchID, result.ResearchName,
result.ResearchType, result.ModelType, result.RealizationCount,
result.Size, result.Edges);
SaveResearchParameters(result.ResearchParameterValues);
SaveGenerationParameters(result.GenerationParameterValues);
for (int i = 0; i < result.EnsembleResults.Count; ++i)
{
SaveEnsembleResult(result.EnsembleResults[i], i);
}*/
workbook.Workbook.Save();
document.Close();
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId3");
GenerateThemePart1Content(themePart1);
ExtendedPart extendedPart1 = workbookPart1.AddExtendedPart("http://schemas.openxmlformats.org/officeDocument/2006/relationships/powerPivotData", "application/vnd.openxmlformats-officedocument.model+data", "data", "rId7");
GenerateExtendedPart1Content(extendedPart1);
PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 = workbookPart1.AddNewPart<PivotTableCacheDefinitionPart>("rId2");
GeneratePivotTableCacheDefinitionPart1Content(pivotTableCacheDefinitionPart1);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPart1Content(worksheetPart1);
TableDefinitionPart tableDefinitionPart1 = worksheetPart1.AddNewPart<TableDefinitionPart>("rId3");
GenerateTableDefinitionPart1Content(tableDefinitionPart1);
QueryTablePart queryTablePart1 = tableDefinitionPart1.AddNewPart<QueryTablePart>("rId1");
GenerateQueryTablePart1Content(queryTablePart1);
SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1 = worksheetPart1.AddNewPart<SpreadsheetPrinterSettingsPart>("rId2");
GenerateSpreadsheetPrinterSettingsPart1Content(spreadsheetPrinterSettingsPart1);
PivotTablePart pivotTablePart1 = worksheetPart1.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart1Content(pivotTablePart1);
pivotTablePart1.AddPart(pivotTableCacheDefinitionPart1, "rId1");
SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId6");
GenerateSharedStringTablePart1Content(sharedStringTablePart1);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId5");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
ConnectionsPart connectionsPart1 = workbookPart1.AddNewPart<ConnectionsPart>("rId4");
GenerateConnectionsPart1Content(connectionsPart1);
SetPackageProperties(document);
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
SlicerCachePart slicerCachePart1 = workbookPart1.AddNewPart<SlicerCachePart>("rId3");
GenerateSlicerCachePart1Content(slicerCachePart1);
SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId7");
GenerateSharedStringTablePart1Content(sharedStringTablePart1);
SlicerCachePart slicerCachePart2 = workbookPart1.AddNewPart<SlicerCachePart>("rId2");
GenerateSlicerCachePart2Content(slicerCachePart2);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPart1Content(worksheetPart1);
TableDefinitionPart tableDefinitionPart1 = worksheetPart1.AddNewPart<TableDefinitionPart>("rId3");
GenerateTableDefinitionPart1Content(tableDefinitionPart1);
TableDefinitionPart tableDefinitionPart2 = worksheetPart1.AddNewPart<TableDefinitionPart>("rId2");
GenerateTableDefinitionPart2Content(tableDefinitionPart2);
DrawingsPart drawingsPart1 = worksheetPart1.AddNewPart<DrawingsPart>("rId1");
GenerateDrawingsPart1Content(drawingsPart1);
SlicersPart slicersPart1 = worksheetPart1.AddNewPart<SlicersPart>("rId4");
GenerateSlicersPart1Content(slicersPart1);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId6");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId5");
GenerateThemePart1Content(themePart1);
SlicerCachePart slicerCachePart3 = workbookPart1.AddNewPart<SlicerCachePart>("rId4");
GenerateSlicerCachePart3Content(slicerCachePart3);
SetPackageProperties(document);
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document, WorkBookData data)
{
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPartContent(workbookPart1, data.sheetNames);
ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId" + (data.data.Tables.Count + 1));
GenerateThemePart1Content(themePart1);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId" + (data.data.Tables.Count + 2));
GenerateWorkbookStylesPartContent(workbookStylesPart1);
int wsID = 1;
WorksheetPart wsPart;
DataRow currentRow;
foreach (DataTable dt in data.data.Tables)
{
wsPart = workbookPart1.AddNewPart<WorksheetPart>("rId" + wsID++);
GenerateWorksheetPartContent(wsPart, dt, wsID <= 2);
//Adding HyperLinkRelationships
int idTracker = 1;
for (int ii = 0; ii < dt.Rows.Count; ii++)
{
currentRow = dt.Rows[ii];
for (int jj = 0; jj < dt.Columns.Count; jj++)
{
if (dt.Columns[jj].DataType == typeof(string) && currentRow[jj].ToString().ToLower().Contains("http"))
{
wsPart.AddHyperlinkRelationship(new System.Uri(currentRow[jj].ToString(), System.UriKind.Absolute), true, "rId" + idTracker++);
}
}
}
}
//SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId4");
//GenerateSharedStringTablePart1Content(sharedStringTablePart1);
SetPackageProperties(document);
}
public OpenXmlExcel(Stream stream)
{
spreadSheet = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
shareStringPart.SharedStringTable = new SharedStringTable();
shareStringPart.SharedStringTable.Count = 1;
shareStringPart.SharedStringTable.UniqueCount = 1;
CurrentWorksheetPart = worksheetPart;
}
//var numberCell = new Cell {
// DataType = CellValues.Number,
// CellReference = header + index,
// CellValue = new CellValue(text),
// StyleIndex = 3
//};
public void Execute()
{
List <Person> personen = GetData();
String pathToTemp = @"..\..\testFolder";
if (!Directory.Exists(pathToTemp))
{
Directory.CreateDirectory(pathToTemp);
}
String tempFile = pathToTemp + "\\" + DateTime.Now.Ticks / TimeSpan.TicksPerMillisecond + ".xlsx";
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(tempFile, SpreadsheetDocumentType.Workbook)) {
// create the workbook
var dateFormat = new NumberingFormat()
{
NumberFormatId = (UInt32Value)0,
FormatCode = StringValue.FromString("dd.MM.yyyy")
};
WorkbookPart part = spreadSheet.AddWorkbookPart();
part.Workbook = new Workbook();
part.AddNewPart <WorksheetPart>();
part.WorksheetParts.First().Worksheet = new Worksheet();
WorkbookStylesPart sp = spreadSheet.WorkbookPart.AddNewPart <WorkbookStylesPart>();
sp.Stylesheet = new Stylesheet();
sp.Stylesheet.NumberingFormats = new NumberingFormats();
sp.Stylesheet.NumberingFormats.Append(dateFormat);
CellFormat cellFormat = new CellFormat();
cellFormat.NumberFormatId = dateFormat.NumberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
sp.Stylesheet.CellFormats = new CellFormats();
sp.Stylesheet.CellFormats.AppendChild <CellFormat>(cellFormat);
sp.Stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)sp.Stylesheet.CellFormats.ChildElements.Count);
sp.Stylesheet.Save();
Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// create sheet data
foreach (Person p in personen)
{
WorksheetPart tabPart = part.AddNewPart <WorksheetPart>();
Worksheet workSheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheet sheet1 = new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(tabPart),
SheetId = 1,
Name = p.Name
};
sheets.Append(sheet1);
Row r = new Row();
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.Name), DataType = CellValues.String
});
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.BirthDay.ToOADate().ToString()), StyleIndex = 0
});
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.HeightInCm.ToString(CultureInfo.InvariantCulture)), DataType = CellValues.Number
});
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.Weight.ToString(CultureInfo.InvariantCulture)), DataType = CellValues.Number
});
sheetData1.AppendChild(r);
workSheet1.AppendChild(sheetData1);
tabPart.Worksheet = workSheet1;
}
part.Workbook.Save();
}
Process.Start(@tempFile);
Environment.Exit(0);
}
private void BuildWorkbook(string filename)
{
try
{
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
var wbp = xl.AddWorkbookPart();
var wsp = wbp.AddNewPart <WorksheetPart>();
var wb = new Workbook();
var fv = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
var ws = new Worksheet();
var sd = new SheetData();
var wbsp = wbp.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
var sImagePath = "polymathlogo.png";
var dp = wsp.AddNewPart <DrawingsPart>();
var imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
{
imgp.FeedData(fs);
}
var nvdp = new NonVisualDrawingProperties
{
Id = 1025,
Name = "Picture 1",
Description = "polymathlogo"
};
var picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks
{
NoChangeAspect = true,
NoChangeArrowheads = true
};
var nvpdp = new NonVisualPictureDrawingProperties
{
PictureLocks = picLocks
};
var nvpp = new NonVisualPictureProperties
{
NonVisualDrawingProperties = nvdp,
NonVisualPictureDrawingProperties = nvpdp
};
var stretch = new DocumentFormat.OpenXml.Drawing.Stretch
{
FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle()
};
var blip = new DocumentFormat.OpenXml.Drawing.Blip
{
Embed = dp.GetIdOfPart(imgp),
CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print
};
var blipFill = new BlipFill
{
Blip = blip,
SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle()
};
blipFill.Append(stretch);
var offset = new DocumentFormat.OpenXml.Drawing.Offset
{
X = 0,
Y = 0
};
var t2d = new DocumentFormat.OpenXml.Drawing.Transform2D
{
Offset = offset
};
var bm = Xwt.Drawing.Image.FromFile(sImagePath).ToBitmap();
//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
var extents = new DocumentFormat.OpenXml.Drawing.Extents
{
Cx = (long)bm.Width * (long)((float)914400 / bm.PixelWidth),
Cy = (long)bm.Height * (long)((float)914400 / bm.PixelHeight)
};
bm.Dispose();
t2d.Extents = extents;
var prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry
{
Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle,
AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList()
};
var sp = new ShapeProperties
{
BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto,
Transform2D = t2d
};
sp.Append(prstGeom);
sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());
var picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture
{
NonVisualPictureProperties = nvpp,
BlipFill = blipFill,
ShapeProperties = sp
};
var pos = new Position {
X = 0, Y = 0
};
Extent ext = new Extent {
Cx = extents.Cx, Cy = extents.Cy
};
var anchor = new AbsoluteAnchor
{
Position = pos,
Extent = ext
};
anchor.Append(picture);
anchor.Append(new ClientData());
var wsd = new WorksheetDrawing();
wsd.Append(anchor);
var drawing = new Drawing {
Id = dp.GetIdOfPart(imgp)
};
wsd.Save(dp);
UInt32 index;
Random rand = new Random();
sd.Append(CreateHeader(10));
sd.Append(CreateColumnHeader(11));
for (index = 12; index < 30; ++index)
{
sd.Append(CreateContent(index, ref rand));
}
ws.Append(sd);
ws.Append(drawing);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet
{
Name = "Sheet1",
SheetId = 1,
Id = wbp.GetIdOfPart(wsp)
};
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
}
}
public override void Export(string filename, LayoutList list)
{
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = xl.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
//add styles
WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
// Add a SharedStringTablePart to the WorkbookPart.
var stringPart = workbookpart.AddNewPart <SharedStringTablePart>();
var stringTable = new StringKeyList();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
SheetData sd = new SheetData();
Worksheet worksheet = new Worksheet(sd);
worksheetPart.Worksheet = worksheet;
// Add Sheets to the Workbook.
Sheets sheets = xl.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = xl.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "DataSheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
//List<ILayoutItem> cols = LayoutMapTool.GetVisibleItems(list.ListInfo.Columns);
//columns
ExpMapLayout(sd, list.ListInfo.Columns, 0, 1, out int mc, out int ind, null, null, stringTable);
//data
if (list.ListInfo.GroupVisible)
{
foreach (LayoutGroup g in list.Groups)
{
ind++;
Cell cell = GetCell(g.TextValue, 0, (int)ind, 8, stringTable);
GetRow(sd, ind, false).Append(cell);
MergeCells mcells = GetMergeCells(worksheet);
MergeCell mcell = new MergeCell()
{
Reference = new StringValue(cell.CellReference + ":" + Helper.IntToChar(mc) + (ind).ToString())
};
mcells.Append(mcell);
for (int i = g.IndexStart; i <= g.IndexEnd; i++)
{
ind++;
ExpMapLayout(sd, list.ListInfo.Columns, 0, ind, out mc, out ind, list, list.ListSource[i], stringTable);
}
}
}
else
{
foreach (object o in list.ListSource)
{
ind++;
ExpMapLayout(sd, list.ListInfo.Columns, 0, ind, out mc, out ind, list, o, stringTable);
}
}
worksheet.Save();
OpenXmlValidator validator = new OpenXmlValidator();
var errors = validator.Validate(xl);
StringBuilder sb = new StringBuilder();
foreach (var error in errors)
{
sb.AppendLine(error.Description);
sb.AppendLine(error.Path.XPath.ToString());
sb.AppendLine();
}
if (sb.Length > 0)
{
//System.Windows.Forms.MessageDialog.ShowMessage(sb.ToString());
}
xl.Close();
}
}
private void BtnExportarExcel(object sender, RoutedEventArgs e)
{
string FileName;
//string filetoOpen;
try
{
String Fecha = DateTime.Now.ToString("dd-MM-yyyy");
String FilePath = "Ventas " + Fecha + ".xlsx ";
FileName = FilePath;
//filetoOpen = FilePath;
using (SpreadsheetDocument spreedDoc = SpreadsheetDocument.Create(FilePath,
DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
WorkbookPart wbPart = spreedDoc.WorkbookPart;
if (wbPart == null)
{
wbPart = spreedDoc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
}
//Pagina 1
string sheetName = "Ventas al Crédito Pendientes";
WorksheetPart worksheetPart = null;
worksheetPart = wbPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
// Create custom widths for columns
Columns lstColumns = new Columns();
lstColumns.Append(new Column()
{
Min = 1, Max = 1, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 2, Max = 2, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 3, Max = 3, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 4, Max = 4, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 5, Max = 5, Width = 15, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 6, Max = 6, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 7, Max = 7, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 8, Max = 8, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 9, Max = 9, Width = 25, CustomWidth = true
});
worksheetPart.Worksheet = new Worksheet(lstColumns, sheetData);
if (wbPart.Workbook.Sheets == null)
{
wbPart.Workbook.AppendChild <Sheets>(new Sheets());
}
var sheet = new Sheet()
{
Id = wbPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = sheetName
};
var workingSheet = ((WorksheetPart)wbPart.GetPartById(sheet.Id)).Worksheet;
List <Venta> VentasPendientes = ViewModel.VentasList.Where(t => t.VentaCompletada == "No").ToList();
Row row1 = new Row();
row1.RowIndex = (UInt32)1;
row1.AppendChild(AddCellWithText("Nombre Cliente"));
row1.AppendChild(AddCellWithText("Cedula Cliente"));
row1.AppendChild(AddCellWithText("Empresa"));
row1.AppendChild(AddCellWithText("N# Pagos"));
row1.AppendChild(AddCellWithText("Fecha Ultimo Pago"));
row1.AppendChild(AddCellWithText("Monto Pagado a la fecha"));
row1.AppendChild(AddCellWithText("Saldo Pendiente"));
row1.AppendChild(AddCellWithText("Monto Total"));
row1.AppendChild(AddCellWithText("Productos Comprado"));
sheetData.AppendChild(row1);
int rowindex = 3;
foreach (var emp in VentasPendientes)
{
Row row = new Row();
row.RowIndex = (UInt32)rowindex;
row.AppendChild(AddCellWithText(emp.Cliente.Nombre));
row.AppendChild(AddCellWithText(emp.Cliente.Cedula));
row.AppendChild(AddCellWithText(emp.Cliente.Compania));
double montoPago = 0;
if (emp.Pagos.Count() == 0)
{
row.AppendChild(AddCellWithText("0"));
row.AppendChild(AddCellWithText("No tiene"));
row.AppendChild(AddCellWithText(montoPago.ToString()));
row.AppendChild(AddCellWithText(emp.MontoVenta.ToString()));
}
else
{
row.AppendChild(AddCellWithText(emp.Pagos.Count().ToString()));
row.AppendChild(AddCellWithText(emp.Pagos.Last().Fecha_Pago.ToString()));
foreach (var i in emp.Pagos)
{
montoPago = i.Monto;
}
row.AppendChild(AddCellWithText(montoPago.ToString()));
row.AppendChild(AddCellWithText((emp.MontoVenta - montoPago).ToString()));
}
row.AppendChild(AddCellWithText(emp.MontoVenta.ToString()));
String productos = "";
if (emp.Especificaciones_producto.Count() == 0)
{
row.AppendChild(AddCellWithText("El producto no está registrado"));
}
else
{
var cantidadProductos = emp.Especificaciones_producto.Count();
//Mostramos los productos comprados
foreach (var i in emp.Especificaciones_producto)
{
if (cantidadProductos > 1)
{
productos = productos + i.Nombre + ", ";
}
else
{
productos = i.Nombre;
}
}
row.AppendChild(AddCellWithText(productos));
}
sheetData.AppendChild(row);
rowindex++;
}
wbPart.Workbook.Sheets.AppendChild(sheet);
//Hoja 2
WorksheetPart worksheetPart2 = null;
worksheetPart2 = wbPart.AddNewPart <WorksheetPart>();
var sheetData2 = new SheetData();
// Create custom widths for columns
Columns lstColumns2 = new Columns();
lstColumns2.Append(new Column()
{
Min = 1, Max = 1, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 2, Max = 2, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 3, Max = 3, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 4, Max = 4, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 5, Max = 5, Width = 15, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 6, Max = 6, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 7, Max = 7, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 8, Max = 8, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 9, Max = 9, Width = 25, CustomWidth = true
});
worksheetPart2.Worksheet = new Worksheet(lstColumns2, sheetData2);
if (wbPart.Workbook.Sheets == null)
{
wbPart.Workbook.AppendChild <Sheets>(new Sheets());
}
var sheet2 = new Sheet()
{
Id = wbPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "Ventas al crédito completadas"
};
List <Venta> VentasCreditoCompletadas = ViewModel.VentasList.Where(t => t.VentaCompletada == "Si" && t.Tipo_Venta == "Crédito").ToList();
var workingSheet2 = ((WorksheetPart)wbPart.GetPartById(sheet2.Id)).Worksheet;
Row row2 = new Row();
row2.RowIndex = (UInt32)1;
row2.AppendChild(AddCellWithText("Nombre Cliente"));
row2.AppendChild(AddCellWithText("Cedula Cliente"));
row2.AppendChild(AddCellWithText("Empresa"));
row2.AppendChild(AddCellWithText("N# Pagos"));
row2.AppendChild(AddCellWithText("Fecha Ultimo Pago"));
row2.AppendChild(AddCellWithText("Monto Pagado a la fecha"));
row2.AppendChild(AddCellWithText("Saldo Pendiente"));
row2.AppendChild(AddCellWithText("Monto Pagado"));
row2.AppendChild(AddCellWithText("Productos Comprados"));
sheetData2.AppendChild(row2);
int rowindex2 = 3;
foreach (var emp in VentasCreditoCompletadas)
{
Row row = new Row();
row.RowIndex = (UInt32)rowindex2;
row.AppendChild(AddCellWithText(emp.Cliente.Nombre));
row.AppendChild(AddCellWithText(emp.Cliente.Cedula));
row.AppendChild(AddCellWithText(emp.Cliente.Compania));
row.AppendChild(AddCellWithText(emp.Pagos.Count().ToString()));
row.AppendChild(AddCellWithText(emp.Pagos.Last().Fecha_Pago.ToString()));
double montoPago = 0;
foreach (var i in emp.Pagos)
{
montoPago = i.Monto;
}
row.AppendChild(AddCellWithText(montoPago.ToString()));
row.AppendChild(AddCellWithText((emp.MontoVenta - montoPago).ToString()));
row.AppendChild(AddCellWithText(emp.MontoVenta.ToString()));
String productos = "";
if (emp.Especificaciones_producto.Count() == 0)
{
row.AppendChild(AddCellWithText("El producto no está registrado"));
}
else
{
var cantidadProductos = emp.Especificaciones_producto.Count();
//Mostramos los productos comprados
foreach (var i in emp.Especificaciones_producto)
{
if (cantidadProductos > 1)
{
productos = productos + i.Nombre + ", ";
}
else
{
productos = i.Nombre;
}
}
row.AppendChild(AddCellWithText(productos));
sheetData2.AppendChild(row);
rowindex2++;
}
wbPart.Workbook.Sheets.AppendChild(sheet2);
}
//Hoja 3
WorksheetPart worksheetPart3 = null;
worksheetPart3 = wbPart.AddNewPart <WorksheetPart>();
var sheetData3 = new SheetData();
// Create custom widths for columns
Columns lstColumns3 = new Columns();
lstColumns3.Append(new Column()
{
Min = 1, Max = 1, Width = 25, CustomWidth = true
});
lstColumns3.Append(new Column()
{
Min = 2, Max = 2, Width = 25, CustomWidth = true
});
lstColumns3.Append(new Column()
{
Min = 3, Max = 3, Width = 25, CustomWidth = true
});
lstColumns3.Append(new Column()
{
Min = 4, Max = 4, Width = 25, CustomWidth = true
});
lstColumns3.Append(new Column()
{
Min = 5, Max = 5, Width = 25, CustomWidth = true
});
lstColumns3.Append(new Column()
{
Min = 6, Max = 6, Width = 25, CustomWidth = true
});
worksheetPart3.Worksheet = new Worksheet(lstColumns3, sheetData3);
if (wbPart.Workbook.Sheets == null)
{
wbPart.Workbook.AppendChild <Sheets>(new Sheets());
}
var sheet3 = new Sheet()
{
Id = wbPart.GetIdOfPart(worksheetPart3),
SheetId = 3,
Name = "Ventas al Contado completadas"
};
List <Venta> VentasContadoCompletadas = ViewModel.VentasList.Where(t => t.VentaCompletada == "Si" && t.Tipo_Venta == "Contado").ToList();
var workingSheet3 = ((WorksheetPart)wbPart.GetPartById(sheet3.Id)).Worksheet;
Row row3 = new Row();
row3.RowIndex = (UInt32)1;
row3.AppendChild(AddCellWithText("Nombre Cliente"));
row3.AppendChild(AddCellWithText("Cedula Cliente"));
row3.AppendChild(AddCellWithText("Empresa"));
row3.AppendChild(AddCellWithText("Fecha Venta"));
row3.AppendChild(AddCellWithText("Monto Total"));
row3.AppendChild(AddCellWithText("Productos Comprados"));
sheetData3.AppendChild(row3);
int rowindex3 = 3;
foreach (var emp in VentasContadoCompletadas)
{
Row row = new Row();
row.RowIndex = (UInt32)rowindex3;
//Validando si esta venta fue minima y no tuvo cliente
if (emp.Cliente == null)
{
row.AppendChild(AddCellWithText("No fue Ingresado"));
row.AppendChild(AddCellWithText("No fue Ingresado"));
row.AppendChild(AddCellWithText("No fue Ingresado"));
}
else
{
row.AppendChild(AddCellWithText(emp.Cliente.Nombre));
row.AppendChild(AddCellWithText(emp.Cliente.Cedula));
row.AppendChild(AddCellWithText(emp.Cliente.Compania));
}
row.AppendChild(AddCellWithText(emp.Pagos.Last().Fecha_Pago.ToString()));
row.AppendChild(AddCellWithText(emp.MontoVenta.ToString()));
String productos = "";
if (emp.Especificaciones_producto.Count() == 0)
{
row.AppendChild(AddCellWithText("El producto no está registrado"));
}
else
{
var cantidadProductos = emp.Especificaciones_producto.Count();
//Mostramos los productos comprados
foreach (var i in emp.Especificaciones_producto)
{
if (cantidadProductos > 1)
{
productos = productos + i.Nombre + ", ";
}
else
{
productos = i.Nombre;
}
}
row.AppendChild(AddCellWithText(productos));
sheetData3.AppendChild(row);
rowindex3++;
}
}
wbPart.Workbook.Sheets.AppendChild(sheet3);
}
MessageBoxResult result = MessageBox.Show("Se ha generado correctamente la hoja de excel, con la fecha actual, en la la carpeta bin/release",
"Confirmation",
MessageBoxButton.OK,
MessageBoxImage.Information);
//Start the process
//Get current process Path
System.Diagnostics.Process process = new System.Diagnostics.Process();
string file = System.IO.Path.Combine(Environment.CurrentDirectory, FileName);
process.StartInfo.FileName = file;
process.Start();
//openexcel
//Process.Start(filetoOpen);
}
catch
{
MessageBoxResult result = MessageBox.Show("Hubo un error al generar la hoja de Excel, revise la documentación por favor.",
"Confirmation",
MessageBoxButton.OK,
MessageBoxImage.Exclamation);
}
}
/// <summary>
/// This method exports datatable to a excel file
/// </summary>
/// <param name="table">DataTable</param>
/// <param name="exportFile">Excel file name</param>
private static void ExportDataTableToExcel(DataTable table, string exportFile)
{
try
{
// Create a spreadsheet document by supplying the filepath.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(exportFile, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
SheetData data = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public ExportContext(SpreadsheetDocument spreadSheet)
{
if(spreadSheet == null)
{
throw new ArgumentNullException("spreadSheet");
}
if(spreadSheet.FileOpenAccess != System.IO.FileAccess.ReadWrite)
{
throw new Exception("No access granted for opened excel");
}
SpreadSheet = spreadSheet;
if(SpreadSheet.WorkbookPart == null)
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = SpreadSheet.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 = SpreadSheet.WorkbookPart.Workbook.
AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = SpreadSheet.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet 1"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
}
WorkbookStylesPart stylesPart = SpreadSheet.WorkbookPart.WorkbookStylesPart;
if(stylesPart == null)
{
stylesPart = SpreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
}
if(stylesPart.Stylesheet == null)
{
stylesPart.Stylesheet = new Stylesheet();
}
StyleSheet = stylesPart.Stylesheet;
if(StyleSheet.Fonts == null)
{
StyleSheet.Fonts = new Fonts();
// required by Excel
StyleSheet.Fonts.AppendChild(new Font());
StyleSheet.Fonts.Count = 1;
}
if(StyleSheet.Fills == null)
{
StyleSheet.Fills = new Fills();
// required, reserved by Excel
StyleSheet.Fills.Append(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } });
// required, reserved by Excel
StyleSheet.Fills.Append(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } });
StyleSheet.Fills.Count = 2;
}
if(StyleSheet.Borders == null)
{
StyleSheet.Borders = new Borders();
// required by Excel
StyleSheet.Borders.Append(new Border());
StyleSheet.Borders.Count = 1;
}
if(StyleSheet.CellStyleFormats == null)
{
StyleSheet.CellStyleFormats = new CellStyleFormats();
// required by Excel
StyleSheet.CellStyleFormats.Append(new CellFormat());
StyleSheet.CellStyleFormats.Count = 1;
}
if(StyleSheet.CellFormats == null)
{
StyleSheet.CellFormats = new CellFormats();
// required by Excel
StyleSheet.CellFormats.AppendChild(new CellFormat());
StyleSheet.CellFormats.Count = 1;
}
Worksheet = SpreadSheet.WorkbookPart.WorksheetParts.First().Worksheet;
SheetData = Worksheet.GetFirstChild<SheetData>();
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document, List<ProjectFile> projectFiles)
{
ExtendedFilePropertiesPart extendedFileProperties = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
GenerateExtendedFilePropertiesContent(extendedFileProperties);
WorkbookPart workbook = document.AddWorkbookPart();
GenerateWorkbookContent(workbook);
WorkbookStylesPart workbookStyles = workbook.AddNewPart<WorkbookStylesPart>("rId2");
GenerateWorkbookStylesContent(workbookStyles);
//from this point is the content
WorksheetPart worksheet = workbook.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetContent(worksheet, projectFiles);
SharedStringTablePart sharedStringTablePart = workbook.AddNewPart<SharedStringTablePart>("rId4");
GenerateSharedStringTablePartContent(sharedStringTablePart);
SetPackageProperties(document);
}
public byte[] BolleVenditaExcel(ReportDS ds)
{
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();
int numeroColonne = 18;
Columns columns = new Columns();
for (int i = 0; i < numeroColonne; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 15;
columns.Append(c);
}
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Bolle vendita"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(ConstructCell("Azienda", CellValues.String, 2));
row.Append(ConstructCell("Tipo", CellValues.String, 2));
row.Append(ConstructCell("Causale", CellValues.String, 2));
row.Append(ConstructCell("Documento", CellValues.String, 2));
row.Append(ConstructCell("Data documento", CellValues.String, 2));
row.Append(ConstructCell("Numero documento", CellValues.String, 2));
row.Append(ConstructCell("Segnalatore", CellValues.String, 2));
row.Append(ConstructCell("Cliente", CellValues.String, 2));
row.Append(ConstructCell("Riga", CellValues.String, 2));
row.Append(ConstructCell("Modello", CellValues.String, 2));
row.Append(ConstructCell("Quantità", CellValues.String, 2));
row.Append(ConstructCell("Prezzo", CellValues.String, 2));
row.Append(ConstructCell("Valore", CellValues.String, 2));
row.Append(ConstructCell("Ordine cliente", CellValues.String, 2));
row.Append(ConstructCell("Data ordine", CellValues.String, 2));
row.Append(ConstructCell("Data richiesta", CellValues.String, 2));
row.Append(ConstructCell("Data conferma", CellValues.String, 2));
row.Append(ConstructCell("Riferimento", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (ReportDS.BOLLE_VENDITARow bolla in ds.BOLLE_VENDITA)
{
Row rowDati = new Row();
rowDati.Append(ConstructCell(bolla.IsAZIENDANull() ? string.Empty : bolla.AZIENDA, CellValues.String, 1));
rowDati.Append(ConstructCell(string.Format("{0}-{1}", bolla.CODICETIPOO, bolla.DESTABTIPOO), CellValues.String, 1));
rowDati.Append(ConstructCell(string.Format("{0}-{1}", bolla.CODICECAUTR, bolla.DESTABCAUTR), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.FULLNUMDOC, CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.DATDOC.ToString("dd/MM/yyyy"), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.NUMDOC, CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.IsSEGNALATORE_RSNull() ? string.Empty : bolla.SEGNALATORE_RS.Trim(), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.RAGIONESOC.Trim(), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.NRRIGA, CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.MODELLO, CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.QTATOT.ToString().Replace(',', '.'), CellValues.Number, 1));
rowDati.Append(ConstructCell(bolla.PREZZOTOT.ToString().Replace(',', '.'), CellValues.Number, 1));
rowDati.Append(ConstructCell(bolla.VALORE.ToString().Replace(',', '.'), CellValues.Number, 1));
rowDati.Append(ConstructCell(bolla.IsFULLNUMDOC_OCNull() ? string.Empty : bolla.FULLNUMDOC_OC, CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.IsDATDOC_OCNull() ? string.Empty : bolla.DATDOC_OC.ToString("dd/MM/yyyy"), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.IsDATA_RICHIESTANull() ? string.Empty : bolla.DATA_RICHIESTA.ToString("dd/MM/yyyy"), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.IsDATA_CONFERMANull() ? string.Empty : bolla.DATA_CONFERMA.ToString("dd/MM/yyyy"), CellValues.String, 1));
rowDati.Append(ConstructCell(bolla.IsRIFERIMENTONull() ? string.Empty : bolla.RIFERIMENTO, CellValues.String, 1));
sheetData.AppendChild(rowDati);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
public byte[] CaricoLavoroExcel(ReportDS ds)
{
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();
int numeroColonne = 38;
Columns columns = new Columns();
for (int i = 0; i < numeroColonne; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 15;
columns.Append(c);
}
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Carico lavoro"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(ConstructCell("Azienda", CellValues.String, 2));
row.Append(ConstructCell("Tipo Lancio", CellValues.String, 2));
row.Append(ConstructCell("Codice", CellValues.String, 2));
row.Append(ConstructCell("Codiceclifo", CellValues.String, 2));
row.Append(ConstructCell("Ragione Soc", CellValues.String, 2));
row.Append(ConstructCell("nummovfase", CellValues.String, 2));
row.Append(ConstructCell("pianificato_sn", CellValues.String, 2));
row.Append(ConstructCell("NomeCommessa", CellValues.String, 2));
row.Append(ConstructCell("segnalatore", CellValues.String, 2));
row.Append(ConstructCell("Codtipomovfase", CellValues.String, 2));
row.Append(ConstructCell("destipomovfase", CellValues.String, 2));
row.Append(ConstructCell("Modello_lancio", CellValues.String, 2));
row.Append(ConstructCell("Desmodello_lancio", CellValues.String, 2));
row.Append(ConstructCell("Idmagazz", CellValues.String, 2));
row.Append(ConstructCell("Modello_wip", CellValues.String, 2));
row.Append(ConstructCell("Desmodello_wip", CellValues.String, 2));
row.Append(ConstructCell("Elencofasi", CellValues.String, 2));
row.Append(ConstructCell("datamovfase", CellValues.String, 2));
row.Append(ConstructCell("Datainizio_odl", CellValues.String, 2));
row.Append(ConstructCell("Dataprimoinvio_odl", CellValues.String, 2));
row.Append(ConstructCell("Documenti_invio", CellValues.String, 2));
row.Append(ConstructCell("Datafine_odl_e_multipla", CellValues.String, 2));
row.Append(ConstructCell("Datafine_fasecommessa", CellValues.String, 2));
row.Append(ConstructCell("Conta_multiple", CellValues.String, 2));
row.Append(ConstructCell("Codiceunimi", CellValues.String, 2));
row.Append(ConstructCell("Qta", CellValues.String, 2));
row.Append(ConstructCell("qtadater", CellValues.String, 2));
row.Append(ConstructCell("Priorita", CellValues.String, 2));
row.Append(ConstructCell("Noteparticolarifase", CellValues.String, 2));
row.Append(ConstructCell("Notaparticolareodl", CellValues.String, 2));
row.Append(ConstructCell("Modello_lancio_mp", CellValues.String, 2));
row.Append(ConstructCell("Desmodello-lancio_mp", CellValues.String, 2));
row.Append(ConstructCell("Impegnatoareparto", CellValues.String, 2));
row.Append(ConstructCell("Internoesterno", CellValues.String, 2));
row.Append(ConstructCell("Fermounasettimana", CellValues.String, 2));
row.Append(ConstructCell("Scaduto", CellValues.String, 2));
row.Append(ConstructCell("Annocarico", CellValues.String, 2));
row.Append(ConstructCell("Settimanacarico", CellValues.String, 2));
row.Append(ConstructCell("Apertodaduegiorni", CellValues.String, 2));
row.Append(ConstructCell("Nota", CellValues.String, 2));
row.Append(ConstructCell("Appoggio", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (ReportDS.ODL_APERTIRow odl_aperto in ds.ODL_APERTI)
{
Row rowDati = new Row();
rowDati.Append(ConstructCell(odl_aperto.IsAZIENDANull() ? string.Empty : odl_aperto.AZIENDA, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDESTIPOLANCIONull() ? string.Empty : odl_aperto.DESTIPOLANCIO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsCODICETIPOONull() ? string.Empty : odl_aperto.CODICETIPOO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsCODICECLIFONull() ? string.Empty : odl_aperto.CODICECLIFO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsRAGIONESOCNull() ? string.Empty : odl_aperto.RAGIONESOC, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsNUMMOVFASENull() ? string.Empty : odl_aperto.NUMMOVFASE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsPIANIFICATO_SNNull() ? string.Empty : odl_aperto.PIANIFICATO_SN, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsNOMECOMMESSANull() ? string.Empty : odl_aperto.NOMECOMMESSA, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsSEGNALATORENull() ? string.Empty : odl_aperto.SEGNALATORE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsCODTIPOMOVFASENull() ? string.Empty : odl_aperto.CODTIPOMOVFASE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDESTIPOMOVFASENull() ? string.Empty : odl_aperto.DESTIPOMOVFASE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsMODELLO_LANCIONull() ? string.Empty : odl_aperto.MODELLO_LANCIO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDESMODELLO_LANCIONull() ? string.Empty : odl_aperto.DESMODELLO_LANCIO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsIDMAGAZZ_WIPNull() ? string.Empty : odl_aperto.IDMAGAZZ_WIP, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsMODELLO_WIPNull() ? string.Empty : odl_aperto.MODELLO_WIP, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDESMODELLO_WIPNull() ? string.Empty : odl_aperto.DESMODELLO_WIP, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsELENCOFASINull() ? string.Empty : odl_aperto.ELENCOFASI, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDATAMOVFASENull() ? string.Empty : odl_aperto.DATAMOVFASE.ToShortDateString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDATAINIZIO_ODLNull() ? string.Empty : odl_aperto.DATAINIZIO_ODL.ToShortDateString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDATAPRIMOINVIO_ODLNull() ? string.Empty : odl_aperto.DATAPRIMOINVIO_ODL.ToShortDateString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDOCUMENTI_INVIONull() ? string.Empty : odl_aperto.DOCUMENTI_INVIO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDATAFINE_ODL_E_MULTIPLANull() ? string.Empty : odl_aperto.DATAFINE_ODL_E_MULTIPLA.ToShortDateString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDATAFINE_FASECOMMESSANull() ? string.Empty : odl_aperto.DATAFINE_FASECOMMESSA.ToShortDateString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsCONTA_MULTIPLENull() ? string.Empty : odl_aperto.CONTA_MULTIPLE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsCODICEUNIMINull() ? string.Empty : odl_aperto.CODICEUNIMI, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsQTANull() ? string.Empty : odl_aperto.QTA.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsQTADATERNull() ? string.Empty : odl_aperto.QTADATER.ToString(), CellValues.Number, 1));
rowDati.Append(ConstructCell(odl_aperto.IsPRIORITANull() ? string.Empty : odl_aperto.PRIORITA.ToString(), CellValues.Number, 1));
rowDati.Append(ConstructCell(odl_aperto.IsNOTEPARTICOLARIFASENull() ? string.Empty : odl_aperto.NOTEPARTICOLARIFASE, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsNOTAPARTICOLAREODLNull() ? string.Empty : odl_aperto.NOTAPARTICOLAREODL, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsMODELLO_LANCIO_MPNull() ? string.Empty : odl_aperto.MODELLO_LANCIO_MP, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsDESMODELLO_LANCIO_MPNull() ? string.Empty : odl_aperto.DESMODELLO_LANCIO_MP, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsIMPEGNATOAREPARTONull() ? string.Empty : odl_aperto.IMPEGNATOAREPARTO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsINTERNOESTERNONull() ? string.Empty : odl_aperto.INTERNOESTERNO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsFERMOUNASETTIMANANull() ? string.Empty : odl_aperto.FERMOUNASETTIMANA, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsSCADUTONull() ? string.Empty : odl_aperto.SCADUTO, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsANNOCARICONull() ? string.Empty : odl_aperto.ANNOCARICO.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsSETTIMANACARICONull() ? string.Empty : odl_aperto.SETTIMANACARICO.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsAPERTODADUEGIORNINull() ? string.Empty : odl_aperto.APERTODADUEGIORNI, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsNOTANull() ? string.Empty : odl_aperto.NOTA, CellValues.String, 1));
rowDati.Append(ConstructCell(odl_aperto.IsAPPOGGIONull() ? string.Empty : odl_aperto.APPOGGIO, CellValues.String, 1));
sheetData.AppendChild(rowDati);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
/// <summary>
/// Получаем данные: SpreadsheetDocument, worksheetPart и sheetData
/// </summary>
/// <param name="context"></param>
/// <param name="spreadsheetDocument"></param>
/// <param name="worksheetPart"></param>
/// <param name="sheetData"></param>
private void GetData(Context context, out SpreadsheetDocument spreadsheetDocument, out WorksheetPart worksheetPart)
{
// В данном методе открывается/создается файл excel и получаем/создаем элементы структуры.
// C комментариями можно разобрать тут:
// https://github.com/DrGennadius/Example-Scripts-for-ELMA-RPA/blob/master/Excel/%D0%A1%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5%20%D0%BD%D0%BE%D0%B2%D0%BE%D0%B3%D0%BE%20%D0%BB%D0%B8%D1%81%D1%82%D0%B0/ScriptActivity.cs
spreadsheetDocument = null;
bool excelExists = File.Exists(context.ExcelFilePath);
if (excelExists)
{
spreadsheetDocument = SpreadsheetDocument.Open(context.ExcelFilePath, true);
}
else
{
spreadsheetDocument = SpreadsheetDocument.Create(context.ExcelFilePath, SpreadsheetDocumentType.Workbook);
}
WorkbookPart workbookPart = excelExists ? spreadsheetDocument.WorkbookPart : spreadsheetDocument.AddWorkbookPart();
if (!excelExists || workbookPart == null)
{
workbookPart.Workbook = new Workbook();
}
Sheet sheet = null;
worksheetPart = null;
if (excelExists)
{
sheet = workbookPart.Workbook.Descendants <Sheet>().FirstOrDefault(s => s.Name == context.SheetName);
if (sheet == null)
{
worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
}
else
{
worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;
}
}
if (worksheetPart == null)
{
worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
}
if (sheet == null)
{
worksheetPart.Worksheet = new Worksheet(new SheetData());
uint newSheetId = 1U;
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
if (sheets != null)
{
Sheet lastSheet = sheets.Elements <Sheet>().OrderBy(x => x.SheetId).LastOrDefault();
if (lastSheet != null)
{
newSheetId = lastSheet.SheetId + 1;
}
}
else
{
sheets = workbookPart.Workbook.AppendChild(new Sheets());
}
sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = newSheetId,
Name = context.SheetName
};
sheets.Append(sheet);
}
}
private void createExcel(DataSet dsSource, string excelFileName)
{
if (dsSource.Tables.Count <= 0)
{
return;
}
string filePath = Path.GetTempFileName();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = workbookPart.Workbook.AppendChild <Sheets>(new Sheets());
var StylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet _styleSheet = addStyles();
StylesPart.Stylesheet = _styleSheet;
StylesPart.Stylesheet.Save();
UInt32 sheetID = 1;
foreach (DataTable source in dsSource.Tables)
{
if (source.Rows.Count <= 0)
{
continue;
}
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);//*
#region Sheet
Sheet presentSheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetID, Name = source.TableName
}; //*
sheetID++;
sheets.Append(presentSheet); //*
//sheetData.Append(presentSheet);
int ColumnsCount = source.Columns.Count;
object[] Header = new object[ColumnsCount];
for (int i = 0; i < ColumnsCount; i++)
{
Header[i] = source.Columns[i].ColumnName;
}
Row headerRow = new Row();
foreach (object column in Header)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ToString());
headerStyle(cell);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
int RowsCount = source.Rows.Count;
for (int r = 0; r < RowsCount; r++)
{
Row newRow = new Row();
for (int c = 0; c < ColumnsCount; c++)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(source.Rows[r][c].ToString());
itemStyle(cell);
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookPart.Workbook.Save();
#endregion
}
}
byte[] Content;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
MemoryStream ms = new MemoryStream();
fs.CopyTo(ms);
Content = System.IO.File.ReadAllBytes(filePath);
}
Response.Clear();
Response.ContentType = "text/csv";
Response.AddHeader("content-disposition", "attachment; filename=" + excelFileName + ".xlsx");
Response.BufferOutput = true;
Response.OutputStream.Write(Content, 0, Content.Length);
HttpContext.Current.Response.Flush(); // Sends all currently buffered output to the client.
HttpContext.Current.Response.SuppressContent = true; // Gets or sets a value indicating whether to send HTTP content to the client.
HttpContext.Current.ApplicationInstance.CompleteRequest();
//System.IO.File.Delete(ExcelFilePath);
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId3");
GenerateThemePart1Content(themePart1);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId2");
GenerateWorksheetPart1Content(worksheetPart1);
DrawingsPart drawingsPart1 = worksheetPart1.AddNewPart<DrawingsPart>("rId1");
GenerateDrawingsPart1Content(drawingsPart1);
DiagramStylePart diagramStylePart1 = drawingsPart1.AddNewPart<DiagramStylePart>("rId3");
GenerateDiagramStylePart1Content(diagramStylePart1);
ExtendedPart extendedPart1 = drawingsPart1.AddExtendedPart("http://schemas.microsoft.com/office/2007/relationships/hdphoto", "image/vnd.ms-photo", "wdp", "rId7");
GenerateExtendedPart1Content(extendedPart1);
DiagramLayoutDefinitionPart diagramLayoutDefinitionPart1 = drawingsPart1.AddNewPart<DiagramLayoutDefinitionPart>("rId2");
GenerateDiagramLayoutDefinitionPart1Content(diagramLayoutDefinitionPart1);
DiagramDataPart diagramDataPart1 = drawingsPart1.AddNewPart<DiagramDataPart>("rId1");
GenerateDiagramDataPart1Content(diagramDataPart1);
ImagePart imagePart1 = drawingsPart1.AddNewPart<ImagePart>("image/png", "rId6");
GenerateImagePart1Content(imagePart1);
DiagramPersistLayoutPart diagramPersistLayoutPart1 = drawingsPart1.AddNewPart<DiagramPersistLayoutPart>("rId5");
GenerateDiagramPersistLayoutPart1Content(diagramPersistLayoutPart1);
DiagramColorsPart diagramColorsPart1 = drawingsPart1.AddNewPart<DiagramColorsPart>("rId4");
GenerateDiagramColorsPart1Content(diagramColorsPart1);
WorksheetPart worksheetPart2 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPart2Content(worksheetPart2);
DrawingsPart drawingsPart2 = worksheetPart2.AddNewPart<DrawingsPart>("rId3");
GenerateDrawingsPart2Content(drawingsPart2);
ChartPart chartPart1 = drawingsPart2.AddNewPart<ChartPart>("rId1");
GenerateChartPart1Content(chartPart1);
ChartColorStylePart chartColorStylePart1 = chartPart1.AddNewPart<ChartColorStylePart>("rId2");
GenerateChartColorStylePart1Content(chartColorStylePart1);
ChartStylePart chartStylePart1 = chartPart1.AddNewPart<ChartStylePart>("rId1");
GenerateChartStylePart1Content(chartStylePart1);
SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1 = worksheetPart2.AddNewPart<SpreadsheetPrinterSettingsPart>("rId2");
GenerateSpreadsheetPrinterSettingsPart1Content(spreadsheetPrinterSettingsPart1);
WorksheetCommentsPart worksheetCommentsPart1 = worksheetPart2.AddNewPart<WorksheetCommentsPart>("rId6");
GenerateWorksheetCommentsPart1Content(worksheetCommentsPart1);
TableDefinitionPart tableDefinitionPart1 = worksheetPart2.AddNewPart<TableDefinitionPart>("rId5");
GenerateTableDefinitionPart1Content(tableDefinitionPart1);
VmlDrawingPart vmlDrawingPart1 = worksheetPart2.AddNewPart<VmlDrawingPart>("rId4");
GenerateVmlDrawingPart1Content(vmlDrawingPart1);
worksheetPart2.AddHyperlinkRelationship(new System.Uri("http://www.ericwhite.com/", System.UriKind.Absolute), true, "rId1");
CalculationChainPart calculationChainPart1 = workbookPart1.AddNewPart<CalculationChainPart>("rId6");
GenerateCalculationChainPart1Content(calculationChainPart1);
SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId5");
GenerateSharedStringTablePart1Content(sharedStringTablePart1);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId4");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
SetPackageProperties(document);
}
public FileResult ExportFindingsReport(int companyId, string sCurrDate)
{
byte[] b;
DateTime curr;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
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;
string loc;
int rr;
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Findings"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Build sheet
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Findings {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 1, Max = 1, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("A3", "Id"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 2, Max = 2, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("B3", "Name"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 3, Max = 3, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("C3", "Weight (dwt)"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 4, Max = 4, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("D3", "Price"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 5, Max = 5, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("E3", "Vendor"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 6, Max = 6, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("F3", "Inventory"); row.Append(cell);
worksheet.Append(oxl.columns);
sd.Append(row);
List <Finding> Findings = db.Findings.Where(v => v.CompanyId == companyId).OrderBy(f => f.Name).Include("Vendor").ToList();
// Content
for (int i = 0; i < Findings.Count(); i++)
{
row = new Row();
rr = 4 + i;
loc = "A" + rr; cell = oxl.SetCellVal(loc, Findings[i].Id); row.Append(cell);
loc = "B" + rr; cell = oxl.SetCellVal(loc, Findings[i].Name); row.Append(cell);
loc = "C" + rr;
if (Findings[i].Weight == null)
{
cell = oxl.SetCellVal(loc, "");
}
else
{
cell = oxl.SetCellVal(loc, Findings[i].Weight.Value);
}
row.Append(cell);
loc = "D" + rr; cell = oxl.SetCellVal(loc, Findings[i].Price); row.Append(cell);
loc = "E" + rr; cell = oxl.SetCellVal(loc, Findings[i].Vendor.Name); row.Append(cell);
loc = "F" + rr; cell = oxl.SetCellVal(loc, Findings[i].Qty); row.Append(cell);
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",
$"Findings as of {currDate}.xlsx"));
}
}
}
private void createSpreadsheet(Stream stream, string sheetName)
{
spreadsheetDoc = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDoc.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName };
sheets.Append(sheet);
workbookpart.Workbook.Save();
}
/*
* report.GetReportData()
*/
public ActionResult Export(Reports report)
{
try
{
var reportData = report.GetReportData(true);
HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true")
{
Path = "/"
});
var context = HttpContext.Response;
context.Buffer = context.BufferOutput = false;
context.Cache.SetCacheability(HttpCacheability.Private);
context.Cache.SetExpires(DateTime.Now);
context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
CharSet = "utf-8"
}.ToString();
context.AppendHeader("Content-Disposition",
new ContentDisposition
{
DispositionType = DispositionTypeNames.Attachment,
FileName = string.Format(CultureInfo.InvariantCulture, RemoveInvalidFilePathCharacters(reportData.title) + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now)
}.ToString()
);
context.AppendHeader("X-Content-Type-Options", "nosniff");
using (MemoryStream mDocument = new MemoryStream())
{
// Using SAX
using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook))
{
List <OpenXmlAttribute> attributes;
document.AddWorkbookPart();
// Stylesheet
WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesheet.Stylesheet = new Stylesheet(new Fonts(
new Font(new Color()
{
Rgb = new HexBinaryValue()
{
Value = "000000"
}
}), // 0
new Font(new Bold()), // 1
new Font(new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
}) // 2
),
new Fills(new Fill()
{
}),
new Borders(new Border()
{
}),
new CellFormats(
new CellFormat()
{
FontId = 0
}, // 0
new CellFormat()
{
FontId = 1, ApplyFont = true
}, // 1
new CellFormat()
{
FontId = 2, ApplyFont = true
} // 2
)
);
stylesheet.Stylesheet.Save();
WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
IDictionary <string, object> firstRow = new Dictionary <string, object>()
{
{ Words.Reports_Name, "" },
{ Words.Reports_Value, "" }
};
if (firstRow != null)
{
int row = 1;
AddLine(writer, row, new string[] { reportData.title }); row++;
AddLine(writer, row, new string[] { "" }); row++;
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col1 = 1;
foreach (var cols in firstRow.Keys.ToList())
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col1) + row),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(cols));
writer.WriteEndElement();
col1++;
}
writer.WriteEndElement();
row++;
for (int i = 0; i < reportData.labels.Length; i++)
{
attributes =
new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col = 1;
var row2 = new Dictionary <string, object>()
{
{ "Name", reportData.labels[i] },
{ "Value", reportData.datasets[0].data[i] }
};
foreach (var key in row2.Keys)
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col) + row)
};
if (row2[key] is decimal)
{
if ((decimal)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
else if (row2[key] is double)
{
if ((double)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : ""));
writer.WriteEndElement();
col++;
}
writer.WriteEndElement();
row++;
}
}
else
{
// Empty row (no data found)
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, "1")
};
writer.WriteStartElement(new Row(), attributes);
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(1) + 1),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(""));
writer.WriteEndElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = "Sheet 1",
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
document.Save();
document.Close();
mDocument.WriteTo(context.OutputStream);
}
}
}
catch (Exception ex)
{
ex.Log();
}
return(null);
}
public byte[] ReportQuantitaExcel(ReportDS ds)
{
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();
int numeroColonne = 4;
Columns columns = new Columns();
for (int i = 0; i < numeroColonne; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 15;
columns.Append(c);
}
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Report"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(ConstructCell("Codice", CellValues.String, 2));
row.Append(ConstructCell("Ragione Soc", CellValues.String, 2));
row.Append(ConstructCell("Somma", CellValues.String, 2));
row.Append(ConstructCell("Percentuale", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (ReportDS.REPORTQUANTITARow reportquantita in ds.REPORTQUANTITA)
{
Row rowDati = new Row();
rowDati.Append(ConstructCell(reportquantita.CODICECLIFO, CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.RAGIONESOC, CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.SOMMA.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.PERC.ToString(), CellValues.String, 1));
sheetData.AppendChild(rowDati);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
public byte[] ReportOrdiniAttiviExcel(List <OrdiniAttiviModel> lista)
{
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();
int numeroColonne = 12;
Columns columns = new Columns();
for (int i = 0; i < numeroColonne; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 15;
columns.Append(c);
}
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Report"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(ConstructCell("Segnalatore", CellValues.String, 2));
row.Append(ConstructCell("Quantità", CellValues.String, 2));
row.Append(ConstructCell("Quantità non spedita", CellValues.String, 2));
row.Append(ConstructCell("Quantità scaduta", CellValues.String, 2));
row.Append(ConstructCell("Quantità annullata", CellValues.String, 2));
row.Append(ConstructCell("Valore", CellValues.String, 2));
row.Append(ConstructCell("Valore non spedito", CellValues.String, 2));
row.Append(ConstructCell("Valore scaduto", CellValues.String, 2));
row.Append(ConstructCell("Valore annullato", CellValues.String, 2));
row.Append(ConstructCell("% scaduto su cliente", CellValues.String, 2));
row.Append(ConstructCell("% scaduto su totale", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (OrdiniAttiviModel reportquantita in lista)
{
Row rowDati = new Row();
rowDati.Append(ConstructCell(reportquantita.Cliente, CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.Quantita.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.QuantitaNonSpedita.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.QuantitaScaduta.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.QuantitaAnnullata.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.Valore.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.ValoreNonSpedito.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.ValoreScaduto.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.ValoreAnnullato.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.PercScadutoCliente.ToString(), CellValues.String, 1));
rowDati.Append(ConstructCell(reportquantita.PercScadutoSulTotale.ToString(), CellValues.String, 1));
sheetData.AppendChild(rowDati);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
/// <summary>
/// Copy sheet to another document
/// </summary>
/// <param name="sourceDoc">Source document</param>
/// <param name="srcSheetName">Name of source sheet</param>
/// <param name="targetDoc">Spreadsheet document to copied</param>
/// <param name="targetIndex">Index of copied sheet in target document</param>
public static void CopyWorksheet(SpreadsheetDocument sourceDoc, String srcSheetName, SpreadsheetDocument targetDoc, uint targetIndex)
{
// Locate the source sheet
if (sourceDoc.WorkbookPart == null)
{
throw new InvalidOperationException("WorkbookPart is not exist in sourceDoc!");
}
if (sourceDoc.WorkbookPart.Workbook.Sheets == null)
{
throw new InvalidOperationException("No sheets exist in sourceDoc!");
}
var srcSheet =
sourceDoc.WorkbookPart.Workbook.Sheets.Descendants <Sheet>()
.FirstOrDefault(a => a.Name == srcSheetName);
if (srcSheet == null)
{
throw new InvalidOperationException(String.Format("No sheet found with name {0}!", srcSheetName));
}
var srcSheetPart = sourceDoc.WorkbookPart.GetPartById(srcSheet.Id) as WorksheetPart;
if (srcSheetPart == null)
{
throw new InvalidOperationException(String.Format("Cannot find worksheet part with Id {0}!", srcSheet.Id));
}
var srcWorkSheet = srcSheetPart.Worksheet;
if (srcWorkSheet == null)
{
throw new InvalidOperationException("Worksheet not exist in source worksheet part!");
}
// Locate the position of target sheet
WorkbookPart tgtWbPart = targetDoc.WorkbookPart ?? targetDoc.AddWorkbookPart();
Sheets tgtSheets = tgtWbPart.Workbook.Sheets ?? tgtWbPart.Workbook.AppendChild <Sheets>(new Sheets());
if (targetIndex > tgtSheets.Count())
{
targetIndex = (uint)tgtSheets.Count();
}
// Create a new worksheet and clone data from original worksheet
var newSheetPart = tgtWbPart.AddNewPart <WorksheetPart>();
newSheetPart.Worksheet = new Worksheet(); //srcWorkSheet.Clone() as Worksheet;
// Create a unique ID for the new worksheet.
uint sheetId = 1;
if (tgtSheets.Elements <Sheet>().Any())
{
sheetId = tgtSheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Add cloned worksheet to target workbook
var newSheet = new Sheet()
{
Id = tgtWbPart.GetIdOfPart(newSheetPart),
SheetId = sheetId,
Name = srcSheet.Name
};
tgtSheets.InsertAt(newSheet, (int)targetIndex);
// Import data from source sheet to target sheet
ImportWorksheet(sourceDoc, srcWorkSheet, targetDoc, newSheetPart.Worksheet);
// Import all necessary resources into target document that referenced by cloned sheet
//ImportResources(sourceDoc, newSheetPart.Worksheet, targetDoc);
// Save it
tgtWbPart.Workbook.Save();
}
private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
{
// Create the Excel file contents. This function is used when creating an Excel file either writing
// to a file, or writing to a MemoryStream.
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
Stylesheet stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string worksheetName = dt.TableName;
// Create worksheet part, and add it to the sheets collection in workbook
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName };
sheets.Append(sheet);
// Append this worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Save();
}
private string FailedDataToExcel(DataTable dt, List <string> errMsgs)
{
// set file name to include the current date and time
var currentTime = DateTime.Now.ToString("dd-MM-yyyy_HH-mm-ss");
var fileName = "failed_" + currentTime + ".xlsx";
var filePath = Path.Combine(_hostingEnvironment.WebRootPath, "failed_" + currentTime + ".xlsx");
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
// create woorkbookpart
WorkbookPart workbookPart = spreadSheetDocument.AddWorkbookPart();
//workbookPart.Workbook = new Workbook();
spreadSheetDocument.WorkbookPart.Workbook = new Workbook();
spreadSheetDocument.WorkbookPart.Workbook.Sheets = new Sheets();
//create worksheetPart
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
// create sheet
Sheets sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>();
Sheet sheet = new Sheet()
{
Id = spreadSheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Failed entries"
};
sheets.Append(sheet);
Row headers = new Row();
// first get headers and store in a row
foreach (DataColumn col in dt.Columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String; // just store as string
cell.CellValue = new CellValue(col.ColumnName); // get column headers for each column in datatable
headers.Append(cell); // add the cell to headers
}
// add error messages header
Cell errorCell = new Cell();
errorCell.DataType = CellValues.String;
errorCell.CellValue = new CellValue("Error message");
headers.Append(errorCell);
sheetData.AppendChild(headers); // add the headers to the sheet
// enumerator for error messages
IEnumerator <string> errMsgEnumerator = errMsgs.GetEnumerator();
foreach (DataRow dtRow in dt.Rows)
{
Row row = new Row(); // this is the spreadsheet row that will be stored in sheetData
foreach (var item in dtRow.ItemArray)
{
Cell cell = new Cell();
cell.DataType = CellValues.String; // just storing as string
cell.CellValue = new CellValue(item.ToString());
row.Append(cell); // add the cell to the row
}
// set errMsg enumerator to next and then get current value and add to row.
errMsgEnumerator.MoveNext();
Cell errCell = new Cell();
errCell.DataType = CellValues.String;
errCell.CellValue = new CellValue(errMsgEnumerator.Current);
row.Append(errCell);
sheetData.AppendChild(row); // add row to the sheet data
}
}
// return the filename so user can download
return(fileName);
}
public async Task <IActionResult> CarsBase()
{
string outFileName = "CarBase_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Hour + DateTime.Now.Minute + ".xlsx";
DataTable dtServices = new System.Data.DataTable();
var outpathLocal = Path.Combine(
Directory.GetCurrentDirectory(), @"wwwroot/OutFiles/CarBasesReports",
outFileName);
//CreateCarsBaseReport(dtServices);
// Формирование табличной части
dtServices.Columns.Add("Модель");
dtServices.Columns.Add("Гос.номер");
dtServices.Columns.Add("Эксплуатирует");
dtServices.Columns.Add("Подразделение");
dtServices.Columns.Add("Владелец");
dtServices.Columns.Add("Закрепление");
dtServices.Columns.Add("Размещение");
dtServices.Columns.Add("Статус");
var cars = _context.Cars
.Include(c => c.CarModel).ThenInclude(c => c.Manufacturer)
.Include(c => c.CarOwner)
.Include(c => c.CarUsers).ThenInclude(c => c.Employee)
.Include(c => c.CarStatuses).ThenInclude(c => c.Unit).ThenInclude(c => c.Firm).ThenInclude(c => c.Employee)
.Include(c => c.CarStatuses).ThenInclude(c => c.Unit).ThenInclude(c => c.Department)
.Include(c => c.CarStatuses).ThenInclude(c => c.Status)
.Include(c => c.CarStatuses).ThenInclude(c => c.Location)
.ToList();
//var cars = _context.Cars.Include(c => c.CarOwner).ToList();
foreach (var item in cars)
{
DataRow row = dtServices.NewRow();
row["Модель"] = item.CarModel.Manufacturer.Name + " " + item.CarModel.Model;
row["Гос.номер"] = item.RegistrationNumber;
row["Владелец"] = item.CarOwner.FullName;
row["Закрепление"] = item.CarUserForView;
if (item.CarStatuses.Count > 0)
{
row["Эксплуатирует"] = item.CarStatuses.LastOrDefault().Unit.Firm.Employee.FullName;
row["Подразделение"] = item.CarStatuses.LastOrDefault().Unit.Department.Name;
row["Размещение"] = item.CarStatuses.LastOrDefault().Location.LocationName;
//row["Статус"] = item.CarStatuses.LastOrDefault().Status.State;
string _outStat = String.Empty;
if (KernelCars.Infrastructure.Utils.carStatus.TryGetValue(item.CarStatuses.LastOrDefault().Status.State, out _outStat))
{
row["Статус"] = _outStat;
}
}
dtServices.Rows.Add(row);
}
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(outpathLocal, SpreadsheetDocumentType.Workbook);
//Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var sheetData = new SheetData();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Автомобили"
};
sheets.Append(sheet);
Row firstRow = new Row();
Cell headerCell = new Cell();
headerCell.DataType = CellValues.String;
headerCell.CellValue = new CellValue("Список легковых авто по состоянию на " + DateTime.Now.ToShortDateString());
firstRow.AppendChild(headerCell);
sheetData.AppendChild(firstRow);
Row headerRow = new Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in dtServices.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 dtServices.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
//if (col == "Гос.номер" || col == "Модель")
//{
cell.DataType = CellValues.String;
//}
//else
//{
// cell.DataType = CellValues.Number;
//}
cell.CellValue = new CellValue(dsrow[col].ToString());//.Replace(",", "."));
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
var memory = new MemoryStream();
//var outpath = Path.Combine(
// Directory.GetCurrentDirectory(), @"wwwroot/OutFiles",
// outFileName);
using (var stream = new FileStream(outpathLocal, FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", Path.GetFileName(outpathLocal)));
}
public void Save(string path, string testName, List <ExtendedResultSheetItem> results)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, 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>();
//Styles
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 = testName
};
sheets.Append(sheet);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//Question headers
Row row = new Row()
{
RowIndex = 1
};
sheetData.Append(row);
InsertCell(row, 1, "Номер", CellValues.String, 0);
InsertCell(row, 2, "Имя/Фамилия", CellValues.String, 0);
InsertCell(row, 3, "Дата", CellValues.String, 0);
InsertCell(row, 4, "Время выполнения", CellValues.String, 0);
InsertCell(row, 5, "Оценка", CellValues.String, 0);
int colIndex = 6;
Dictionary <string, int> questionTitles = new Dictionary <string, int>();
foreach (var result in results)
{
foreach (var extResult in result.ExtendedResult)
{
if (!questionTitles.ContainsKey(extResult.QuestionName))
{
questionTitles.Add(extResult.QuestionName, colIndex);
InsertCell(row, colIndex++, ReplaceHexadecimalSymbols(extResult.QuestionName), CellValues.String, 0);
}
}
}
//Results
uint rowIndex = 2;
foreach (var result in results)
{
row = new Row()
{
RowIndex = rowIndex++
};
sheetData.Append(row);
InsertCell(row, 1, ReplaceHexadecimalSymbols(result.id.ToString()), CellValues.Number, 1);
InsertCell(row, 2, ReplaceHexadecimalSymbols(result.NameSurname), CellValues.String, 0);
InsertCell(row, 3, ReplaceHexadecimalSymbols(result.PassDate.ToString("dd.MM.yyyy")), CellValues.String, 2);
InsertCell(row, 4, ReplaceHexadecimalSymbols(result.PassingTime.ToString("hh:mm:ss")), CellValues.String, 3);
InsertCell(row, 5, ReplaceHexadecimalSymbols(result.Mark.ToString()), CellValues.Number, (uint)(result.Mark < 4 ? 5 : 4));
foreach (var extResult in result.ExtendedResult)
{
InsertCell(row, questionTitles[extResult.QuestionName], extResult.IsRight ? ReplaceHexadecimalSymbols(extResult.Question_score.ToString()) : "0", CellValues.Number, (uint)(extResult.IsRight ? 4 : 5));
}
}
workbookPart.Workbook.Save();
document.Close();
MessageBox.Show("Успешно сохранено", "Вывод в Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static void CreateDoc(Info 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>();
// Создаем SharedStringTable, если его нет
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 = "C1"
});
uint rowIndex = 2;
foreach (var pc in info.Routes)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = pc.Name,
StyleIndex = 0U
});
rowIndex++;
foreach (var reserve in pc.Reserves)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = reserve.Name,
StyleIndex = 1U
});
rowIndex++;
}
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
public ActionResult ConvertToExcel()
{
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
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();
var list = GetPerson();
Row r1 = new Row();
Cell c1 = new Cell();
Cell c2 = new Cell();
Cell c3 = new Cell();
Cell c4 = new Cell();
c1.DataType = CellValues.String;
c2.DataType = CellValues.String;
c3.DataType = CellValues.String;
c4.DataType = CellValues.String;
c1.CellValue = new CellValue("Ad");
c2.CellValue = new CellValue("Soyad");
c3.CellValue = new CellValue("Adres");
c4.CellValue = new CellValue("Email");
r1.Append(c1);
r1.Append(c2);
r1.Append(c3);
r1.Append(c4);
sd.Append(r1);
foreach (var item in list.Persons)
{
Row r2 = new Row();
Cell c5 = new Cell();
Cell c6 = new Cell();
Cell c7 = new Cell();
Cell c8 = new Cell();
c5.DataType = CellValues.String;
c6.DataType = CellValues.String;
c7.DataType = CellValues.String;
c8.DataType = CellValues.String;
c5.CellValue = new CellValue(item.Name);
c6.CellValue = new CellValue(item.Surname);
c7.CellValue = new CellValue(item.Address);
c8.CellValue = new CellValue(item.Email);
r2.Append(c5);
r2.Append(c6);
r2.Append(c7);
r2.Append(c8);
sd.Append(r2);
}
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
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();
string fileName = "testOpenXml.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
Response.BinaryWrite(dt);
Response.Flush();
Response.End();
return(View());
}
public virtual void Export(string fileName, LayoutList list)
{
this.list = list;
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = xl.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
//add styles
WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
// Add a SharedStringTablePart to the WorkbookPart.
var stringPart = workbookpart.AddNewPart <SharedStringTablePart>();
var stringTable = new StringKeyList();
// Add Sheets to the Workbook.
var sheets = xl.WorkbookPart.Workbook.AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = xl.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "DataSheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
mcells = new List <MergeCell>();
writer = OpenXmlWriter.Create(worksheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetProperties());
writer.WriteElement(new OutlineProperties()
{
SummaryBelow = false, SummaryRight = false
});
writer.WriteEndElement();
mc = 0;
writer.WriteStartElement(new Columns());
WriteMapColumns(list.ListInfo.Columns, 0, 0);
writer.WriteEndElement();
writer.WriteStartElement(new SheetData());
int ind = 1;
var row = new Row()
{
RowIndex = (uint)ind, Height = 25
};
row.AppendChild(GetCell(list.Description, 0, ind, (uint)13, stringTable));
WriteRows(writer, new List <Row>(new Row[] { row }));
mcells.Add(new MergeCell()
{
Reference = new CellRange(0, 1, mc - 1, 1).ToString()
});
WriteMapItem(list.ListInfo.Columns, -1, null, 0, 0, ref ind, stringTable);
if (list.Selection.Count > 1)
{
var items = list.Selection.GetItems <object>();
for (var i = 0; i < items.Count; i++)
{
var item = items[i];
WriteMapItem(list.ListInfo.Columns, i, item, 0, 0, ref ind, stringTable);
}
}
else if (list.NodeInfo != null)
{
var items = list.NodeInfo.Nodes.GetTopLevel().ToList();
for (var i = 0; i < items.Count; i++)
{
var item = items[i] as Node;
WriteMapItem(list.ListInfo.Columns, i, item, 0, 0, ref ind, stringTable);
}
}
else if (list.ListInfo.GroupVisible)
{
foreach (LayoutGroup g in list.Groups)
{
this.group = g;
if (list.ListInfo.GroupHeader)
{
ind++;
var header = new Row()
{
RowIndex = (uint)ind, CustomHeight = true, Height = 20
};
header.AppendChild(GetCell(g.TextValue, 0, ind, 8, stringTable));
mcells.Add(new MergeCell()
{
Reference = new CellRange(0, ind, mc - 1, ind).ToString()
});
WriteRow(writer, header);
}
for (int i = g.IndexStart; i <= g.IndexEnd; i++)
{
WriteMapItem(list.ListInfo.Columns, i, list.ListSource[i], 0, 0, ref ind, stringTable);
}
if (list.ListInfo.CollectingRow)
{
WriteMapItem(list.ListInfo.Columns, -2, null, 0, 0, ref ind, stringTable);
}
//ind++;
}
}
else
{
for (int i = 0; i < list.ListSource.Count; i++)
{
WriteMapItem(list.ListInfo.Columns, i, list.ListSource[i], 0, 0, ref ind, stringTable);
}
if (list.ListInfo.CollectingRow)
{
WriteMapItem(list.ListInfo.Columns, -2, null, 0, 0, ref ind, stringTable);
}
}
writer.WriteEndElement();
if (mcells.Count > 0)
{
writer.WriteStartElement(new MergeCells());
foreach (var cell in mcells)
{
writer.WriteElement(cell);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.Close();
}
}
static void Main(string[] args)
{
IEnumerable <Customer> reportData = Report.GetCustomers();
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create("CustomersReport_Styled.xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart wBookPart = spreadsheetDoc.AddWorkbookPart();
wBookPart.Workbook = new Workbook();
//Creamos nuevo objeto de hojas
spreadsheetDoc.WorkbookPart.Workbook.Sheets = new Sheets();
//para facilitar la manipulación del objeto sheets
Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
//para facilitar el manejo del worksheetpart
WorksheetPart wSheetPart = wBookPart.AddNewPart <WorksheetPart>();
//crea parte para estilos
WorkbookStylesPart stylesPart = spreadsheetDoc.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = StylesheetGenerator.GenerateStyleSheet(); //método para hoja de estilos
stylesPart.Stylesheet.Save();
Columns columns = new Columns();
columns.Append(new Column {
Width = 30, Min = 1, Max = 8
});
Sheet sheet = new Sheet()
{
Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(wSheetPart),
SheetId = 1,
Name = "Hoja_Con_Estilos",
};
sheets.Append(sheet);
SheetData sheetData = new SheetData();
wSheetPart.Worksheet = new Worksheet(columns, sheetData);
Row headerRow = new Row();
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Name"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Register Date"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Last Buy"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Product"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Cost"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Quantity"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Total"
}
});
sheetData.AppendChild(headerRow);
foreach (Customer data in reportData)
{
Row contentRow = new Row();
contentRow.Append(new Cell {
StyleIndex = 3, DataType = CellValues.String, CellValue = new CellValue {
Text = data.Name
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.RegisterDate
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.LastBuy
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.Item
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = data.Quantity.ToString()
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = data.ItemCost.ToString()
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = string.Format("{0}", data.Quantity * data.ItemCost)
}
});
sheetData.AppendChild(contentRow);
}
}
}
public void CreateExcelDoc(string fileName)
{
List <Person> people = new List <Person>();
Initizalize(people);
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "People"
};
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// step 3
DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
sheets.Append(sheet);
workbookPart.Workbook.Save();
// step 4
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.AppendChild(new EditingLanguage()
{
Val = "en-US"
});
Chart chart = chartPart.ChartSpace.AppendChild(new Chart());
chart.AppendChild(new AutoTitleDeleted()
{
Val = true
}); // We don't want to show the chart title
// step 5
PlotArea plotArea = chart.AppendChild(new PlotArea());
Layout layout = plotArea.AppendChild(new Layout());
BarChart barChart = plotArea.AppendChild(new BarChart(
new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
},
new VaryColors()
{
Val = false
}
));
// Constructing header
Row row = new Row();
int rowIndex = 1;
// first empty
row.AppendChild(ConstructCell(string.Empty, CellValues.String));
foreach (var month in Months.Short)
{
row.AppendChild(ConstructCell(month, CellValues.String));
}
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
rowIndex++;
// step 6
for (int i = 0; i < people.Count; i++)
{
BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(
new Index()
{
Val = (uint)i
},
new Order()
{
Val = (uint)i
},
new SeriesText(new NumericValue()
{
Text = people[i].Name
})
));
// Adding category axis to the chart
CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData());
// Category
// Constructing the chart category
string formulaCat = "People!$B$1:$M$1";
StringReference stringReference = categoryAxisData.AppendChild(new StringReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula()
{
Text = formulaCat
}
});
StringCache stringCache = stringReference.AppendChild(new StringCache());
stringCache.Append(new PointCount()
{
Val = (uint)Months.Short.Length
});
for (int j = 0; j < Months.Short.Length; j++)
{
stringCache.AppendChild(new NumericPoint()
{
Index = (uint)j
}).Append(new NumericValue(Months.Short[j]));
}
}
var chartSeries = barChart.Elements <BarChartSeries>().GetEnumerator();
// step 7
for (int i = 0; i < people.Count; i++)
{
row = new Row();
row.AppendChild(ConstructCell(people[i].Name, CellValues.String));
chartSeries.MoveNext();
string formulaVal = string.Format("People!$B${0}:$M${0}", rowIndex);
DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values());
NumberReference numberReference = values.AppendChild(new NumberReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula()
{
Text = formulaVal
}
});
NumberingCache numberingCache = numberReference.AppendChild(new NumberingCache());
numberingCache.Append(new PointCount()
{
Val = (uint)Months.Short.Length
});
for (uint j = 0; j < people[i].Values.Length; j++)
{
var value = people[i].Values[j];
row.AppendChild(ConstructCell(value.ToString(), CellValues.Number));
numberingCache.AppendChild(new NumericPoint()
{
Index = j
}).Append(new NumericValue(value.ToString()));
}
sheetData.AppendChild(row);
rowIndex++;
}
barChart.AppendChild(new DataLabels(
new ShowLegendKey()
{
Val = false
},
new ShowValue()
{
Val = false
},
new ShowCategoryName()
{
Val = false
},
new ShowSeriesName()
{
Val = false
},
new ShowPercent()
{
Val = false
},
new ShowBubbleSize()
{
Val = false
}
));
barChart.Append(new AxisId()
{
Val = 48650112u
});
barChart.Append(new AxisId()
{
Val = 48672768u
});
// step 8
// Adding Category Axis
plotArea.AppendChild(
new CategoryAxis(
new AxisId()
{
Val = 48650112u
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = 48672768u
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new AutoLabeled()
{
Val = true
},
new LabelAlignment()
{
Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center)
}
)
);
// Adding Value Axis
plotArea.AppendChild(
new ValueAxis(
new AxisId()
{
Val = 48672768u
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left)
},
new MajorGridlines(),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "General",
SourceLinked = true
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = 48650112u
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new CrossBetween()
{
Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between)
}
)
);
chart.Append(
new PlotVisibleOnly()
{
Val = true
},
new DisplayBlanksAs()
{
Val = new EnumValue <DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap)
},
new ShowDataLabelsOverMaximum()
{
Val = false
}
);
chartPart.ChartSpace.Save();
// step 9
// Positioning the chart on the spreadsheet
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(
new ColumnId("0"),
new ColumnOffset("0"),
new RowId((rowIndex + 2).ToString()),
new RowOffset("0")
));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(
new ColumnId("8"),
new ColumnOffset("0"),
new RowId((rowIndex + 12).ToString()),
new RowOffset("0")
));
// Append GraphicFrame to TwoCellAnchor
GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new GraphicFrame());
graphicFrame.Macro = string.Empty;
graphicFrame.Append(new NonVisualGraphicFrameProperties(
new NonVisualDrawingProperties()
{
Id = 2u,
Name = "Sample Chart"
},
new NonVisualGraphicFrameDrawingProperties()
));
graphicFrame.Append(new Transform(
new DocumentFormat.OpenXml.Drawing.Offset()
{
X = 0L, Y = 0L
},
new DocumentFormat.OpenXml.Drawing.Extents()
{
Cx = 0L, Cy = 0L
}
));
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Graphic(
new DocumentFormat.OpenXml.Drawing.GraphicData(
new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
}
)
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}
));
twoCellAnchor.Append(new ClientData());
drawingsPart.WorksheetDrawing.Save();
worksheetPart.Worksheet.Save();
}
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId8");
GenerateWorksheetPart1Content(worksheetPart1);
TimeLinePart timeLinePart1 = worksheetPart1.AddNewPart<TimeLinePart>("rId3");
GenerateTimeLinePart1Content(timeLinePart1);
DrawingsPart drawingsPart1 = worksheetPart1.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart1Content(drawingsPart1);
ChartPart chartPart1 = drawingsPart1.AddNewPart<ChartPart>("rId2");
GenerateChartPart1Content(chartPart1);
ChartPart chartPart2 = drawingsPart1.AddNewPart<ChartPart>("rId1");
GenerateChartPart2Content(chartPart2);
PivotTablePart pivotTablePart1 = worksheetPart1.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart1Content(pivotTablePart1);
PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 = pivotTablePart1.AddNewPart<PivotTableCacheDefinitionPart>("rId1");
GeneratePivotTableCacheDefinitionPart1Content(pivotTableCacheDefinitionPart1);
PivotTableCacheRecordsPart pivotTableCacheRecordsPart1 = pivotTableCacheDefinitionPart1.AddNewPart<PivotTableCacheRecordsPart>("rId1");
GeneratePivotTableCacheRecordsPart1Content(pivotTableCacheRecordsPart1);
workbookPart1.AddPart(pivotTableCacheDefinitionPart1, "rId13");
TimeLineCachePart timeLineCachePart1 = workbookPart1.AddNewPart<TimeLineCachePart>("rId18");
GenerateTimeLineCachePart1Content(timeLineCachePart1);
WorksheetPart worksheetPart2 = workbookPart1.AddNewPart<WorksheetPart>("rId3");
GenerateWorksheetPart2Content(worksheetPart2);
TimeLinePart timeLinePart2 = worksheetPart2.AddNewPart<TimeLinePart>("rId3");
GenerateTimeLinePart2Content(timeLinePart2);
DrawingsPart drawingsPart2 = worksheetPart2.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart2Content(drawingsPart2);
ChartPart chartPart3 = drawingsPart2.AddNewPart<ChartPart>("rId2");
GenerateChartPart3Content(chartPart3);
ChartPart chartPart4 = drawingsPart2.AddNewPart<ChartPart>("rId1");
GenerateChartPart4Content(chartPart4);
PivotTablePart pivotTablePart2 = worksheetPart2.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart2Content(pivotTablePart2);
PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart2 = pivotTablePart2.AddNewPart<PivotTableCacheDefinitionPart>("rId1");
GeneratePivotTableCacheDefinitionPart2Content(pivotTableCacheDefinitionPart2);
PivotTableCacheRecordsPart pivotTableCacheRecordsPart2 = pivotTableCacheDefinitionPart2.AddNewPart<PivotTableCacheRecordsPart>("rId1");
GeneratePivotTableCacheRecordsPart2Content(pivotTableCacheRecordsPart2);
TimeLineCachePart timeLineCachePart2 = workbookPart1.AddNewPart<TimeLineCachePart>("rId21");
GenerateTimeLineCachePart2Content(timeLineCachePart2);
WorksheetPart worksheetPart3 = workbookPart1.AddNewPart<WorksheetPart>("rId7");
GenerateWorksheetPart3Content(worksheetPart3);
TimeLinePart timeLinePart3 = worksheetPart3.AddNewPart<TimeLinePart>("rId3");
GenerateTimeLinePart3Content(timeLinePart3);
DrawingsPart drawingsPart3 = worksheetPart3.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart3Content(drawingsPart3);
ChartPart chartPart5 = drawingsPart3.AddNewPart<ChartPart>("rId2");
GenerateChartPart5Content(chartPart5);
ChartPart chartPart6 = drawingsPart3.AddNewPart<ChartPart>("rId1");
GenerateChartPart6Content(chartPart6);
PivotTablePart pivotTablePart3 = worksheetPart3.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart3Content(pivotTablePart3);
pivotTablePart3.AddPart(pivotTableCacheDefinitionPart1, "rId1");
workbookPart1.AddPart(pivotTableCacheDefinitionPart2, "rId12");
TimeLineCachePart timeLineCachePart3 = workbookPart1.AddNewPart<TimeLineCachePart>("rId17");
GenerateTimeLineCachePart3Content(timeLineCachePart3);
SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId25");
GenerateSharedStringTablePart1Content(sharedStringTablePart1);
WorksheetPart worksheetPart4 = workbookPart1.AddNewPart<WorksheetPart>("rId2");
GenerateWorksheetPart4Content(worksheetPart4);
TableDefinitionPart tableDefinitionPart1 = worksheetPart4.AddNewPart<TableDefinitionPart>("rId3");
GenerateTableDefinitionPart1Content(tableDefinitionPart1);
TableDefinitionPart tableDefinitionPart2 = worksheetPart4.AddNewPart<TableDefinitionPart>("rId2");
GenerateTableDefinitionPart2Content(tableDefinitionPart2);
TableDefinitionPart tableDefinitionPart3 = worksheetPart4.AddNewPart<TableDefinitionPart>("rId1");
GenerateTableDefinitionPart3Content(tableDefinitionPart3);
TimeLineCachePart timeLineCachePart4 = workbookPart1.AddNewPart<TimeLineCachePart>("rId16");
GenerateTimeLineCachePart4Content(timeLineCachePart4);
TimeLineCachePart timeLineCachePart5 = workbookPart1.AddNewPart<TimeLineCachePart>("rId20");
GenerateTimeLineCachePart5Content(timeLineCachePart5);
WorksheetPart worksheetPart5 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPart5Content(worksheetPart5);
TableDefinitionPart tableDefinitionPart4 = worksheetPart5.AddNewPart<TableDefinitionPart>("rId3");
GenerateTableDefinitionPart4Content(tableDefinitionPart4);
TableDefinitionPart tableDefinitionPart5 = worksheetPart5.AddNewPart<TableDefinitionPart>("rId2");
GenerateTableDefinitionPart5Content(tableDefinitionPart5);
TableDefinitionPart tableDefinitionPart6 = worksheetPart5.AddNewPart<TableDefinitionPart>("rId1");
GenerateTableDefinitionPart6Content(tableDefinitionPart6);
WorksheetPart worksheetPart6 = workbookPart1.AddNewPart<WorksheetPart>("rId6");
GenerateWorksheetPart6Content(worksheetPart6);
TimeLinePart timeLinePart4 = worksheetPart6.AddNewPart<TimeLinePart>("rId3");
GenerateTimeLinePart4Content(timeLinePart4);
DrawingsPart drawingsPart4 = worksheetPart6.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart4Content(drawingsPart4);
ChartPart chartPart7 = drawingsPart4.AddNewPart<ChartPart>("rId2");
GenerateChartPart7Content(chartPart7);
ChartPart chartPart8 = drawingsPart4.AddNewPart<ChartPart>("rId1");
GenerateChartPart8Content(chartPart8);
PivotTablePart pivotTablePart4 = worksheetPart6.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart4Content(pivotTablePart4);
pivotTablePart4.AddPart(pivotTableCacheDefinitionPart1, "rId1");
WorksheetPart worksheetPart7 = workbookPart1.AddNewPart<WorksheetPart>("rId11");
GenerateWorksheetPart7Content(worksheetPart7);
TimeLinePart timeLinePart5 = worksheetPart7.AddNewPart<TimeLinePart>("rId3");
GenerateTimeLinePart5Content(timeLinePart5);
DrawingsPart drawingsPart5 = worksheetPart7.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart5Content(drawingsPart5);
ChartPart chartPart9 = drawingsPart5.AddNewPart<ChartPart>("rId2");
GenerateChartPart9Content(chartPart9);
ChartPart chartPart10 = drawingsPart5.AddNewPart<ChartPart>("rId1");
GenerateChartPart10Content(chartPart10);
PivotTablePart pivotTablePart5 = worksheetPart7.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart5Content(pivotTablePart5);
pivotTablePart5.AddPart(pivotTableCacheDefinitionPart1, "rId1");
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId24");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
WorksheetPart worksheetPart8 = workbookPart1.AddNewPart<WorksheetPart>("rId5");
GenerateWorksheetPart8Content(worksheetPart8);
TimeLinePart timeLinePart6 = worksheetPart8.AddNewPart<TimeLinePart>("rId3");
GenerateTimeLinePart6Content(timeLinePart6);
DrawingsPart drawingsPart6 = worksheetPart8.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart6Content(drawingsPart6);
ChartPart chartPart11 = drawingsPart6.AddNewPart<ChartPart>("rId2");
GenerateChartPart11Content(chartPart11);
ChartPart chartPart12 = drawingsPart6.AddNewPart<ChartPart>("rId1");
GenerateChartPart12Content(chartPart12);
PivotTablePart pivotTablePart6 = worksheetPart8.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart6Content(pivotTablePart6);
pivotTablePart6.AddPart(pivotTableCacheDefinitionPart1, "rId1");
TimeLineCachePart timeLineCachePart6 = workbookPart1.AddNewPart<TimeLineCachePart>("rId15");
GenerateTimeLineCachePart6Content(timeLineCachePart6);
ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId23");
GenerateThemePart1Content(themePart1);
WorksheetPart worksheetPart9 = workbookPart1.AddNewPart<WorksheetPart>("rId10");
GenerateWorksheetPart9Content(worksheetPart9);
TimeLinePart timeLinePart7 = worksheetPart9.AddNewPart<TimeLinePart>("rId3");
GenerateTimeLinePart7Content(timeLinePart7);
DrawingsPart drawingsPart7 = worksheetPart9.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart7Content(drawingsPart7);
ChartPart chartPart13 = drawingsPart7.AddNewPart<ChartPart>("rId2");
GenerateChartPart13Content(chartPart13);
ChartPart chartPart14 = drawingsPart7.AddNewPart<ChartPart>("rId1");
GenerateChartPart14Content(chartPart14);
PivotTablePart pivotTablePart7 = worksheetPart9.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart7Content(pivotTablePart7);
pivotTablePart7.AddPart(pivotTableCacheDefinitionPart1, "rId1");
TimeLineCachePart timeLineCachePart7 = workbookPart1.AddNewPart<TimeLineCachePart>("rId19");
GenerateTimeLineCachePart7Content(timeLineCachePart7);
WorksheetPart worksheetPart10 = workbookPart1.AddNewPart<WorksheetPart>("rId4");
GenerateWorksheetPart10Content(worksheetPart10);
TimeLinePart timeLinePart8 = worksheetPart10.AddNewPart<TimeLinePart>("rId3");
GenerateTimeLinePart8Content(timeLinePart8);
DrawingsPart drawingsPart8 = worksheetPart10.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart8Content(drawingsPart8);
ChartPart chartPart15 = drawingsPart8.AddNewPart<ChartPart>("rId2");
GenerateChartPart15Content(chartPart15);
ChartPart chartPart16 = drawingsPart8.AddNewPart<ChartPart>("rId1");
GenerateChartPart16Content(chartPart16);
PivotTablePart pivotTablePart8 = worksheetPart10.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart8Content(pivotTablePart8);
pivotTablePart8.AddPart(pivotTableCacheDefinitionPart1, "rId1");
WorksheetPart worksheetPart11 = workbookPart1.AddNewPart<WorksheetPart>("rId9");
GenerateWorksheetPart11Content(worksheetPart11);
TimeLinePart timeLinePart9 = worksheetPart11.AddNewPart<TimeLinePart>("rId3");
GenerateTimeLinePart9Content(timeLinePart9);
DrawingsPart drawingsPart9 = worksheetPart11.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart9Content(drawingsPart9);
ChartPart chartPart17 = drawingsPart9.AddNewPart<ChartPart>("rId2");
GenerateChartPart17Content(chartPart17);
ChartPart chartPart18 = drawingsPart9.AddNewPart<ChartPart>("rId1");
GenerateChartPart18Content(chartPart18);
PivotTablePart pivotTablePart9 = worksheetPart11.AddNewPart<PivotTablePart>("rId1");
GeneratePivotTablePart9Content(pivotTablePart9);
pivotTablePart9.AddPart(pivotTableCacheDefinitionPart1, "rId1");
TimeLineCachePart timeLineCachePart8 = workbookPart1.AddNewPart<TimeLineCachePart>("rId14");
GenerateTimeLineCachePart8Content(timeLineCachePart8);
TimeLineCachePart timeLineCachePart9 = workbookPart1.AddNewPart<TimeLineCachePart>("rId22");
GenerateTimeLineCachePart9Content(timeLineCachePart9);
SetPackageProperties(document);
}
public ActionResult Search(FormCollection model)
{
string selectedReport = model["cboReports"];
string datePeriod = model["DatePeriod"];
if (selectedReport.HasValue())
{
Guid idReport = new Guid(selectedReport);
Reports report = Reports.Get(idReport);
IEnumerable <dynamic> data = Reports.Run(report, datePeriod);
HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true")
{
Path = "/"
});
var context = HttpContext.Response;
context.Buffer = context.BufferOutput = false;
context.Cache.SetCacheability(HttpCacheability.Private);
context.Cache.SetExpires(DateTime.Now);
//context.ContentType = (new ContentType("text/csv") { CharSet = "utf-8" }).ToString(); // CSV
//context.ContentType = (new ContentType("application/vnd.ms-excel") { CharSet = "utf-8" }).ToString();
context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
CharSet = "utf-8"
}.ToString();
context.AppendHeader("Content-Disposition",
new ContentDisposition
{
DispositionType = DispositionTypeNames.Attachment,
FileName = string.Format(CultureInfo.InvariantCulture, report.FileNamePrefix + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now)
}.ToString()
);
context.AppendHeader("X-Content-Type-Options", "nosniff");
using (MemoryStream mDocument = new MemoryStream())
{
// Using SAX
using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook))
{
List <OpenXmlAttribute> attributes;
document.AddWorkbookPart();
// Stylesheet
WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesheet.Stylesheet = new Stylesheet(new Fonts(
new Font( // 0 = Default
new Color()
{
Rgb = new HexBinaryValue()
{
Value = "000000"
}
}
),
new Font( // 1 = Bold
new Bold()
),
new Font( // 2 = Red
new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
}
)
),
new Fills(
new Fill()
{
}
),
new Borders(new Border()
{
}),
new CellFormats(
new CellFormat()
{
FontId = 0
}, // 0
new CellFormat()
{
FontId = 1, ApplyFont = true
}, // 1
new CellFormat()
{
FontId = 2, ApplyFont = true
} // 2
)
);
stylesheet.Stylesheet.Save();
WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
IDictionary <string, object> firstRow = data.FirstOrDefault();
if (firstRow != null)
{
int row = 1;
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col1 = 1;
foreach (var cols in firstRow.Keys.ToList())
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col1) + row),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(cols));
writer.WriteEndElement();
col1++;
}
writer.WriteEndElement();
row++;
foreach (IDictionary <string, object> row2 in data)
{
attributes =
new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col = 1;
foreach (var key in row2.Keys)
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col) + row)
};
if (row2[key] is decimal)
{
if ((decimal)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
else if (row2[key] is double)
{
if ((double)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : ""));
writer.WriteEndElement();
col++;
}
writer.WriteEndElement();
row++;
}
}
else
{
// Empty row (no data found)
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, "1")
};
writer.WriteStartElement(new Row(), attributes);
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(1) + 1),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(""));
writer.WriteEndElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = "Sheet 1",
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
document.Save();
document.Close();
mDocument.WriteTo(context.OutputStream);
}
}
return(null);
}
return(null);
}
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();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Export"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Заявка", CellValues.String),
ConstructCell("Статус", CellValues.String),
ConstructCell("Дата Создания", CellValues.String),
ConstructCell("Создатель", CellValues.String),
ConstructCell("Улица", CellValues.String),
ConstructCell("Дом", CellValues.String),
ConstructCell("Корпус", CellValues.String),
ConstructCell("Квартира", CellValues.String),
ConstructCell("Телефоны", CellValues.String),
ConstructCell("Услуга", CellValues.String),
ConstructCell("Причина", CellValues.String),
ConstructCell("Примечание", CellValues.String),
ConstructCell("Дата", CellValues.String),
ConstructCell("Время", CellValues.String),
ConstructCell("Мастер", CellValues.String),
ConstructCell("Исполнитель", CellValues.String),
ConstructCell("Выполнение С", CellValues.String),
ConstructCell("Выполнение По", CellValues.String),
ConstructCell("Потрачено Времени", CellValues.String),
ConstructCell("Гарантийная", CellValues.String),
ConstructCell("Оценка", CellValues.String),
ConstructCell("Комментарий К Оценке", CellValues.String),
ConstructCell("Повторная", CellValues.String),
ConstructCell("Аварийная", CellValues.String)
);
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
foreach (var request in RequestList)
{
{
row = new Row();
row.Append(
ConstructCell(request.Id.ToString(), CellValues.Number),
ConstructCell(request.Status, CellValues.String),
ConstructCell(request.CreateTime.ToString("dd.MM.yyyy HH:mm"), CellValues.String),
ConstructCell(request.CreateUser.ShortName, CellValues.String),
ConstructCell(request.StreetName, CellValues.String),
ConstructCell(request.Building, CellValues.String),
ConstructCell(request.Corpus, CellValues.String),
ConstructCell(request.Flat, CellValues.String),
ConstructCell(request.ContactPhones, CellValues.String),
ConstructCell(request.ParentService, CellValues.String),
ConstructCell(request.Service, CellValues.String),
ConstructCell(request.Description, CellValues.String),
ConstructCell(request.ExecuteTime?.Date.ToString("dd.MM.yyyy") ?? "", CellValues.String),
ConstructCell(request.ExecutePeriod, CellValues.String),
ConstructCell(request.Master?.ShortName, CellValues.String),
ConstructCell(request.Executer?.ShortName, CellValues.String),
ConstructCell(request.FromTime?.ToString("HH:mm:ss") ?? "", CellValues.String),
ConstructCell(request.ToTime?.ToString("HH:mm:ss") ?? "", CellValues.String),
ConstructCell(request.SpendTime, CellValues.String),
ConstructCell(request.GarantyTest, CellValues.String),
ConstructCell(request.Rating, CellValues.String),
ConstructCell(request.RatingDescription, CellValues.String),
ConstructCell(request.IsRetry ? "Да" : "", CellValues.String),
ConstructCell(request.ImmediateText, CellValues.String));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
}
}
}
public ExcelDocument(string filename)
{
if (File.Exists(filename))
{
document = SpreadsheetDocument.Open(filename, true);
if (document.WorkbookPart.GetPartsOfType<WorkbookStylesPart>().Count() == 0)
{
WorkbookStylesPart stylespart = document.WorkbookPart.AddNewPart<WorkbookStylesPart>();
stylespart.Stylesheet = GetStylesheet();
DateTypeIndex = 1;
stylesheet = stylespart.Stylesheet;
}
else
{
WorkbookStylesPart stylespart = document.WorkbookPart.GetPartsOfType<WorkbookStylesPart>().First();
IEnumerable<CellFormat> cellformats = stylespart.Stylesheet.CellFormats.Elements<CellFormat>();
if (cellformats.Where(f => f.NumberFormatId == 14).Count() > 0)
{
uint dateformatindex = 0;
foreach (CellFormat format in cellformats)
if (format.NumberFormatId == 14) break; else dateformatindex++;
DateTypeIndex = dateformatindex;
}
else
{
stylespart.Stylesheet.CellFormats.Append(new CellFormat()
{
BorderId = 0,
FillId = 0,
FontId = 0,
NumberFormatId = 14,
FormatId = 0,
ApplyNumberFormat = true
});
stylespart.Stylesheet.CellFormats.Count = (uint)stylespart.Stylesheet.CellFormats.ChildElements.Count;
DateTypeIndex = stylespart.Stylesheet.CellFormats.Count - 1;
}
stylesheet = stylespart.Stylesheet;
}
}
else
{
document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorkbookStylesPart stylespart = workbookpart.AddNewPart<WorkbookStylesPart>();
stylespart.Stylesheet = GetStylesheet();
DateTypeIndex = 1;
stylesheet = stylespart.Stylesheet;
}
Sheets = new ExcelSheetCollection(this);
}
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 = "Е1"
});
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 = "С",
RowIndex = 2,
Text = "Цена",
StyleIndex = 0U
});
uint i = 1;
foreach (var route in info.Routes)
{
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 = route.RouteName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = i + 2,
Text = route.Cost.ToString(),
StyleIndex = 0U
});
i++;
}
workbookpart.Workbook.Save();
}
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
WorkbookPart workbookPart1 = document.AddWorkbookPart();
wbPart = workbookPart1;
GenerateWorkbookPart1Content(workbookPart1);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("R3c8458136e1d4b6c");
GenerateWorksheetPart1Content(worksheetPart1);
wsSheet = worksheetPart1;
SetPackageProperties(document);
}
public byte[] GenerateExcel(List <System.Data.DataTable> _DataSet, string Classification)
{
var stream = new MemoryStream();
SpreadsheetDocument document = SpreadsheetDocument.Create(stream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = document.AddWorkbookPart();
wbp.Workbook = new Workbook();
Sheets sheets = new Sheets();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
UInt32 TotalSheets = 1;
WorksheetPart[] wsp = new WorksheetPart[TotalSheets];
//Worksheet[] ws = new Worksheet[TotalSheets];
SheetData[] sd = new SheetData[TotalSheets];
Sheet[] sheet = new Sheet[TotalSheets];
Columns thisColumn = new Columns();
for (int i = 0; i < TotalSheets; i++)
{
wsp[i] = wbp.AddNewPart <WorksheetPart>();
sd[i] = new SheetData();
wsp[i].Worksheet = new Worksheet();
wsp[i].Worksheet.Append(thisColumn);
wsp[i].Worksheet.Append(sd[i]);
sheet[i] = new Sheet();
}
WorkbookStylesPart wbsp = wbp.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
_GetExcelInfo _ge = new _GetExcelInfo();
UInt32 HeaderRow = 1;
for (int i = 0; i < TotalSheets; i++)
{
DataColumnCollection _dccColumnID = _DataSet[i].Columns;
if (_dccColumnID.Contains("rownumb"))
{
_DataSet[i].Columns.Remove("rownumb");
}
if (_dccColumnID.Contains("SSN"))
{
_DataSet[i].Columns.Remove("SSN");
}
if (_dccColumnID.Contains("DOC_MAP")) //Catch all to rename this column whenever present.
{
_DataSet[i].Columns["DOC_MAP"].ColumnName = "ASG";
}
CreateColumnHeader(_DataSet[i], sd[i], _ge, HeaderRow);
CreateHeaderFooter(Classification, wsp, sd, i);
CreateContent(_DataSet[i], sd[i], HeaderRow, _ge);
AutoSizeColumns(wsp, sd, thisColumn, i);
}
for (UInt32 i = 0; i < TotalSheets; i++)
{
//wsp[i].Worksheet.Append(sd[i]);
wsp[i].Worksheet.Save();
sheet[i].SheetId = i + 1;
sheet[i].Name = "Sheet " + (i + 1);
sheet[i].Id = wbp.GetIdOfPart(wsp[i]);
sheets.Append(sheet[i]);
}
wbp.Workbook.Append(fv);
wbp.Workbook.Append(sheets);
document.WorkbookPart.Workbook.Save();
document.Close();
return(stream.ToArray());
}
/// <summary>
/// Create a new spreadsheet with a custom theme.
/// </summary>
/// <param name="ThemeSettings">Custom theme settings.</param>
public SLDocument(SLThemeSettings ThemeSettings)
{
memstream = new MemoryStream();
xl = SpreadsheetDocument.Create(memstream, SpreadsheetDocumentType.Workbook);
wbp = xl.AddWorkbookPart();
IsNewSpreadsheet = true;
slwb = new SLWorkbook();
this.DocumentProperties = new SLDocumentProperties();
this.DocumentProperties.Created = DateTime.UtcNow.ToString(SLConstants.W3CDTF);
InitialiseAutoFitCache();
LoadBuiltInNumberingFormats();
InitialiseStylesheetWhatNots(ThemeSettings);
LoadSharedStringTable();
InitialiseNewSpreadsheet();
}
public FileResult ExportLocationReport(int CompanyId, string sCurrDate)
{
byte[] b;
DateTime curr;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
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;
string loc;
int rr;
// Build sheet
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Locations"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Location {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 1, Max = 1, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("A3", "Name"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 2, Max = 2, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("B3", "Short Name"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 3, Max = 3, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("C3", "Phone"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 4, Max = 4, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("D3", "Email"); row.Append(cell);
worksheet.Append(oxl.columns);
sd.Append(row);
List <Presenter> locations = db.Presenters.Where(x => x.CompanyId == CompanyId).ToList();
// Content
for (int i = 0; i < locations.Count(); i++)
{
row = new Row();
rr = 4 + i;
loc = "A" + rr; cell = oxl.SetCellVal(loc, locations[i].Name); row.Append(cell);
loc = "B" + rr; cell = oxl.SetCellVal(loc, locations[i].ShortName); row.Append(cell);
loc = "C" + rr; cell = oxl.SetCellVal(loc, locations[i].Phone); row.Append(cell);
loc = "D" + rr; cell = oxl.SetCellVal(loc, locations[i].Email); row.Append(cell);
sd.Append(row);
}
worksheet.Append(sd);
// Autofit columns - ss:AutoFitWidth="1"
worksheetPart.Worksheet = worksheet;
workbookPart.Workbook.Save();
document.Close();
b = memStream.ToArray();
Company company = db.FindCompany(CompanyId);
return(File(b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
company.Name + $" Locations as of {currDate}.xlsx"));
}
}
}
// Adds child parts and generates content of the specified part
private static void CreateParts(SpreadsheetDocument document)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
// Add a WorkbookPart to the document.
WorkbookPart starterWorkbookPart = document.AddWorkbookPart();
starterWorkbookPart.Workbook = new Workbook();
starterWorkbookPart.Workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart starterWorksheetPart = starterWorkbookPart.AddNewPart<WorksheetPart>();
// SheetData: Represents a cell table. Expresses information about each cell, grouped together by rows in the worksheet.
starterWorksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets firstSheets = document.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet leadingSheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(starterWorksheetPart),
SheetId = 1,
Name = "Sheet1"
};
firstSheets.Append(leadingSheet);
// Save the document
starterWorkbookPart.Workbook.Save();
// Close the document
document.Close();
}
public MemoryStream CreateExcelDoc(List <RemittanceExcelModel> Remittances, 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 = 3,
Width = 15,
CustomWidth = true
},
new Column // Salary column
{
Min = 4,
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 = "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("Счет 1", CellValues.String, 2),
ConstructCell("Счет 2", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
foreach (var remittance in Remittances)
{
row = new Row();
row.Append(
ConstructCell(remittance.Id.ToString(), CellValues.Number, 1),
ConstructCell(remittance.ActionDate.ToString("yyyy/MM/dd"), CellValues.String, 1),
ConstructCell(remittance.Sum.ToString(), CellValues.String, 1),
ConstructCell(remittance.Score, CellValues.String, 1),
ConstructCell(remittance.Score2, CellValues.String, 1));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
document.Close();
return(mem);
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
var context = new SaveContext();
var workbookPart = document.WorkbookPart ?? document.AddWorkbookPart();
var worksheets = WorksheetsInternal;
var partsToRemove = workbookPart.Parts.Where(s => worksheets.Deleted.Contains(s.RelationshipId)).ToList();
partsToRemove.ForEach(s => workbookPart.DeletePart(s.OpenXmlPart));
context.RelIdGenerator.AddValues(workbookPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook);
var extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ??
document.AddNewPart<ExtendedFilePropertiesPart>(
context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateExtendedFilePropertiesPartContent(extendedFilePropertiesPart);
GenerateWorkbookPartContent(workbookPart, context);
var sharedStringTablePart = workbookPart.SharedStringTablePart ??
workbookPart.AddNewPart<SharedStringTablePart>(
context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateSharedStringTablePartContent(sharedStringTablePart, context);
var workbookStylesPart = workbookPart.WorkbookStylesPart ??
workbookPart.AddNewPart<WorkbookStylesPart>(
context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateWorkbookStylesPartContent(workbookStylesPart, context);
foreach (var worksheet in WorksheetsInternal.Cast<XLWorksheet>().OrderBy(w => w.Position))
{
//context.RelIdGenerator.Reset(RelType.);
WorksheetPart worksheetPart;
var wsRelId = worksheet.RelId;
if (workbookPart.Parts.Any(p => p.RelationshipId == wsRelId))
{
worksheetPart = (WorksheetPart)workbookPart.GetPartById(wsRelId);
var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList();
wsPartsToRemove.ForEach(tdp => worksheetPart.DeletePart(tdp));
}
else
worksheetPart = workbookPart.AddNewPart<WorksheetPart>(wsRelId);
context.RelIdGenerator.AddValues(worksheetPart.HyperlinkRelationships.Select(hr => hr.Id).ToList(),
RelType.Workbook);
context.RelIdGenerator.AddValues(worksheetPart.Parts.Select(p => p.RelationshipId).ToList(),
RelType.Workbook);
if (worksheetPart.DrawingsPart != null)
context.RelIdGenerator.AddValues(
worksheetPart.DrawingsPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook);
// delete comment related parts (todo: review)
DeleteComments(worksheetPart, worksheet, context);
if (worksheet.Internals.CellsCollection.GetCells(c => c.HasComment).Any())
{
var worksheetCommentsPart =
worksheetPart.AddNewPart<WorksheetCommentsPart>(context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateWorksheetCommentsPartContent(worksheetCommentsPart, worksheet);
//VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>(worksheet.LegacyDrawingId);
var vmlDrawingPart = worksheetPart.VmlDrawingParts.FirstOrDefault();
if (vmlDrawingPart == null)
{
if (XLHelper.IsNullOrWhiteSpace(worksheet.LegacyDrawingId))
{
worksheet.LegacyDrawingId = context.RelIdGenerator.GetNext(RelType.Workbook);
worksheet.LegacyDrawingIsNew = true;
}
vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>(worksheet.LegacyDrawingId);
}
GenerateVmlDrawingPartContent(vmlDrawingPart, worksheet, context);
}
GenerateWorksheetPartContent(worksheetPart, worksheet, context);
if (worksheet.PivotTables.Any())
{
GeneratePivotTables(workbookPart, worksheetPart, worksheet, context);
}
//DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>("rId1");
//GenerateDrawingsPartContent(drawingsPart, worksheet);
//foreach (var chart in worksheet.Charts)
//{
// ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>("rId1");
// GenerateChartPartContent(chartPart, (XLChart)chart);
//}
}
GenerateCalculationChainPartContent(workbookPart, context);
if (workbookPart.ThemePart == null)
{
var themePart = workbookPart.AddNewPart<ThemePart>(context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateThemePartContent(themePart);
}
if (CustomProperties.Any())
{
document.GetPartsOfType<CustomFilePropertiesPart>().ToList().ForEach(p => document.DeletePart(p));
var customFilePropertiesPart =
document.AddNewPart<CustomFilePropertiesPart>(context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateCustomFilePropertiesPartContent(customFilePropertiesPart);
}
SetPackageProperties(document);
}
static void CreateExcelReport(int x)
{
log.Info("Thread " + x + " started");
Console.WriteLine("Thread " + x + " started");
string filePath = @"e:\SolbegSoft\Excel\" + Guid.NewGuid() + ".xlsx";
int rowNumber = RandomNumber(300000, 500000);
int skipNumber = RandomNumber(0, 1500000);
log.Info(filePath + " " + skipNumber + " " + rowNumber);
Console.WriteLine(filePath + " " + skipNumber + " " + rowNumber);
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
xl.CompressionOption = CompressionOption.SuperFast;
//IEnumerable<FakeObject> objects = db.Objects.OrderBy(ob => ob.Id).Skip(skipNumber).Take(rowNumber);
xl.AddWorkbookPart();
WorksheetPart wsp = xl.WorkbookPart.AddNewPart <WorksheetPart>();
using (var oxw = OpenXmlWriter.Create(wsp))
{
oxw.WriteStartElement(new Worksheet());
oxw.WriteStartElement(new SheetData());
int chunkSize = 40000;
var ostatok = rowNumber % chunkSize;
int chunksCount = ostatok == 0 ? (rowNumber / chunkSize) : (rowNumber / chunkSize + 1);
List <string> headers = new List <string>();
headers.Add("1");
headers.Add("2");
headers.Add("3");
headers.Add("4");
headers.Add("5");
headers.Add("6");
headers.Add("7");
headers.Add("8");
headers.Add("9");
headers.Add("10");
headers.Add("11");
WriteRow(headers, oxw, 1);
try
{
Enumerable.Range(0, chunksCount).ToList().ForEach(number =>
{
var currentChunkSize = number == chunksCount - 1 ? ostatok : chunkSize;
FakeDbContext db = new FakeDbContext();
log.Info("Thread " + x + " chunk " + number);
Console.WriteLine("Thread " + x + " chunk " + number);
var chunk =
db.Objects.OrderBy(ob => ob.Id)
.Skip(skipNumber + number * chunkSize)
.Take(currentChunkSize)
.ToList();
WriteRow(chunk, oxw, number * chunkSize + 2);
});
}
catch (Exception e)
{
log.Error(e.Message);
}
// this is for SheetData
oxw.WriteEndElement();
// this is for Worksheet
oxw.WriteEndElement();
oxw.Close();
using (var oxw1 = OpenXmlWriter.Create(xl.WorkbookPart))
{
oxw1.WriteStartElement(new Workbook());
oxw1.WriteStartElement(new Sheets());
oxw1.WriteElement(new Sheet()
{
Name = "Sheet1",
SheetId = 1,
Id = xl.WorkbookPart.GetIdOfPart(wsp)
});
// this is for Sheets
oxw1.WriteEndElement();
// this is for Workbook
oxw1.WriteEndElement();
oxw1.Close();
}
}
xl.Close();
}
log.Info("Thread " + x + " finished");
Console.WriteLine("Thread " + x + " finished");
}
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 = "I1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 2,
Text = "ФИО клиента",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A2",
CellToName = "C2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = 2,
Text = "Дата заказа",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "D2",
CellToName = "E2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = 2,
Text = "Сумма к оплате",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "F2",
CellToName = "G2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "H",
RowIndex = 2,
Text = "Статус",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "H2",
CellToName = "I2"
});
uint rowIndex = 1;
foreach (var sr in info.Orders)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex + 2,
Text = sr.ClientFIO,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex + 2,
Text = sr.DateCreate.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = rowIndex + 2,
Text = (sr.Price - sr.Sum).ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "H",
RowIndex = rowIndex + 2,
Text = sr.Status.ToString(),
StyleIndex = 0U
});
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
WebExTaskpanesPart webExTaskpanesPart1 = document.AddNewPart<WebExTaskpanesPart>("rId2");
GenerateWebExTaskpanesPart1Content(webExTaskpanesPart1);
WebExtensionPart webExtensionPart1 = webExTaskpanesPart1.AddNewPart<WebExtensionPart>("rId2");
GenerateWebExtensionPart1Content(webExtensionPart1);
WebExtensionPart webExtensionPart2 = webExTaskpanesPart1.AddNewPart<WebExtensionPart>("rId1");
GenerateWebExtensionPart2Content(webExtensionPart2);
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId3");
GenerateThemePart1Content(themePart1);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId2");
GenerateWorksheetPart1Content(worksheetPart1);
DrawingsPart drawingsPart1 = worksheetPart1.AddNewPart<DrawingsPart>("rId1");
GenerateDrawingsPart1Content(drawingsPart1);
WebExtensionPart webExtensionPart3 = drawingsPart1.AddNewPart<WebExtensionPart>("rId3");
GenerateWebExtensionPart3Content(webExtensionPart3);
ImagePart imagePart1 = webExtensionPart3.AddNewPart<ImagePart>("image/png", "rId1");
GenerateImagePart1Content(imagePart1);
ImagePart imagePart2 = drawingsPart1.AddNewPart<ImagePart>("image/png", "rId2");
GenerateImagePart2Content(imagePart2);
WebExtensionPart webExtensionPart4 = drawingsPart1.AddNewPart<WebExtensionPart>("rId1");
GenerateWebExtensionPart4Content(webExtensionPart4);
webExtensionPart4.AddPart(imagePart2, "rId1");
drawingsPart1.AddPart(imagePart1, "rId4");
WorksheetPart worksheetPart2 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPart2Content(worksheetPart2);
DrawingsPart drawingsPart2 = worksheetPart2.AddNewPart<DrawingsPart>("rId1");
GenerateDrawingsPart2Content(drawingsPart2);
ImagePart imagePart3 = drawingsPart2.AddNewPart<ImagePart>("image/png", "rId2");
GenerateImagePart3Content(imagePart3);
WebExtensionPart webExtensionPart5 = drawingsPart2.AddNewPart<WebExtensionPart>("rId1");
GenerateWebExtensionPart5Content(webExtensionPart5);
webExtensionPart5.AddPart(imagePart3, "rId1");
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId4");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId4");
GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);
SetPackageProperties(document);
}
