/// <summary>
/// XDocument containing Xml content of the styles part
/// </summary>
public static XDocument GetStylesDocument(SpreadsheetDocument document)
{
if (document.WorkbookPart.WorkbookStylesPart != null)
return document.WorkbookPart.WorkbookStylesPart.GetXDocument();
else
return null;
}
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);
}
public static Cell XLGetCell(SpreadsheetDocument document, string sheetName, string columnName, uint rowIndex)
{
Sheet theSheet = XLGetSheet(document, sheetName);
// Throw an exception if there is no sheet.
if (theSheet == null) throw new ArgumentException("sheetName");
return XLGetCell(document, theSheet, columnName, rowIndex);
}
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 static string XLGetCellValue(SpreadsheetDocument document, string sheetName, string columnName, uint rowIndex)
{
Cell cell = XLGetCell(document, sheetName, columnName, rowIndex);
string value = null;
if (cell != null) value = XLInterpretedCellValue(document, cell);
return value;
}
/// <summary>
/// Creates the content of the shet (columns, rows, cells)
/// </summary>
/// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
/// <param name="worksheetPart">The worksheetpart for this item</param>
private void InsertTextIntoCells(SpreadsheetDocument spreadsheetDocument, WorksheetPart worksheetPart)
{
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Any())
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
#region Excel headers
string header1 = "ResourceTypeID";
int index1 = _sharedResources.InsertSharedStringItem(header1, shareStringPart);
Cell headerCell1 = _sharedResources.InsertCellInWorksheet("A", 1, worksheetPart);
headerCell1.CellValue = new CellValue(index1.ToString(CultureInfo.InvariantCulture));
headerCell1.DataType = new EnumValue<CellValues>(CellValues.SharedString);
string header2 = "ResourceType";
int index2 = _sharedResources.InsertSharedStringItem(header2, shareStringPart);
Cell headerCell2 = _sharedResources.InsertCellInWorksheet("B", 1, worksheetPart);
headerCell2.CellValue = new CellValue(index2.ToString(CultureInfo.InvariantCulture));
headerCell2.DataType = new EnumValue<CellValues>(CellValues.SharedString);
#endregion
#region insert the items from the temporary list into the ktUIOrder excel sheet
int columnCount = 1;
uint rowCount = 2;
foreach (ktResourceType resourceType in _resourceType.ktResourceTypeList)
{
if (columnCount >= 2)
{
columnCount = 1;
}
string text1 = resourceType.ResourceTypeID;
Cell cell1 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
cell1.CellValue = new CellValue(text1);
cell1.DataType = CellValues.Number;
columnCount++;
string text2 = resourceType.ResourceType;
Cell cell2 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
cell2.CellValue = new CellValue(text2);
cell2.DataType = CellValues.String;
columnCount++;
rowCount++;
}
#endregion
worksheetPart.Worksheet.Save();
}
private ExcelStreamReader(string fileName)
{
this.fileName = fileName;
this.doc = SpreadsheetDocument.Open(fileName, false);
this.workbookPart = doc.WorkbookPart;
this.sheets = GetSheets();
}
// 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);
}
/// <summary>
/// Creates a spreadsheet document from a value table
/// </summary>
/// <param name="filePath">Path to store the document</param>
/// <param name="headerList">Contents of first row (header)</param>
/// <param name="valueTable">Contents of data</param>
/// <param name="initialRow">Row to start copying data from</param>
/// <returns></returns>
public static void Create(SpreadsheetDocument document, List<string> headerList, string[][] valueTable, int initialRow)
{
headerRow = initialRow;
//Creates a worksheet with given data
WorksheetPart worksheet = WorksheetAccessor.Create(document, headerList, valueTable, headerRow);
}
// 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);
}
public xlBook ReadToBook(SpreadsheetDocument document)
{
using (_docProvider = new DocDictionaries(document, _config.AutoDispose))
{
xlBook result = new xlBook();
for (int i = 0; i < document.WorkbookPart.WorksheetParts.Count(); i++)
{
result.AddSheet(_docProvider.sheetNames[i]);
var sheet = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().SingleOrDefault(s => s.Name == _docProvider.sheetNames[i]);
var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id.Value);
foreach (var cell in worksheetPart.Worksheet.GetFirstChild<SheetData>().Descendants<Cell>())
{
try
{
if (cell == null) { continue; }
result[i].Cells.Add(ReadCell(cell));
}
catch (Exception)
{
if (_config.ContinueOnRowReadingError) continue; else throw;
}
}
}
return result;
}
}
public DocDictionaries(SpreadsheetDocument document, bool AutoDispose)
{
_autoDispose = AutoDispose;
this.document = document;
sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable.Select(x => x.InnerText).ToArray();
sheetNames = document.WorkbookPart.Workbook.Sheets.Cast<Sheet>().Select(x => x.Name.Value).ToArray();
styles = document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Cast<CellFormat>().ToArray();
}
/// <summary>
/// Создает экземпляр класса для работы с текущим файлом
/// </summary>
/// <param name="filePath">Путь к документу</param>
/// <param name="removeAfterDestroy">Удалять ли файл после окончания работы с ним</param>
public ExcellWorker(string filePath, bool removeAfterDestroy)
{
_currentFilePath = filePath;
_currentDocument = SpreadsheetDocument.Open(filePath, true);
_currentWorkBookPart = _currentDocument.WorkbookPart;
_currentSheet = _currentWorkBookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
RemoveAfterDestroy = removeAfterDestroy;
}
public RenderSpreadSheetDocument_OpenXmlSdk(Report report, IStreamGen sg)
: base(report, sg)
{
_ms = new MemoryStream();
_spreadSheet = SpreadsheetDocument.Create(_ms, SpreadsheetDocumentType.Workbook);
_openXmlExportHelper = new OpenXmlWriterHelper();
_workbook = new Workbook();
_workSheetSettings = new List<WorkSheetSetting>();
}
private void UpdateParts(SpreadsheetDocument document, Dictionary<WorksheetPart, List<ExcelSheet>> worksheetsWithRows, List<ProjectFile> projectFiles, Customer customer, User user)
{
var workSheets = document.WorkbookPart.WorksheetParts;
foreach (var workSheet in workSheets)
{
UpdateWorksheetContent(workSheet, worksheetsWithRows[workSheet], document.WorkbookPart.SharedStringTablePart.SharedStringTable, projectFiles, customer, user);
}
}
public ExcelDataReader(string path, int sheetIndex = 0, bool firstRowAsHeader = true)
{
_document = SpreadsheetDocument.Open(path, false);
_sharedStrings = GetSharedStrings(_document);
var worksheetPart = _document.WorkbookPart.GetPartById(GetSheetByIndex(sheetIndex).Id.Value);
_reader = OpenXmlReader.Create(worksheetPart);
_headers = firstRowAsHeader ? GetFirstRowAsHeaders() : GetRangeHeaders(worksheetPart);
}
protected ExcelTemplate(string fileName)
{
mStream = new MemoryStream();
using (var fileStream = File.OpenRead(fileName))
{
fileStream.CopyTo(mStream);
}
mSpreadSheet = SpreadsheetDocument.Open(mStream, true);
}
public ExcelDataReader(Stream stream, string sheetName, bool firstRowAsHeader = true)
{
_document = SpreadsheetDocument.Open(stream, false);
_sharedStrings = GetSharedStrings(_document);
var worksheetPart = _document.WorkbookPart.GetPartById(GetSheetByName(sheetName).Id.Value);
_reader = OpenXmlReader.Create(worksheetPart);
_headers = firstRowAsHeader ? GetFirstRowAsHeaders() : GetRangeHeaders(worksheetPart);
}
public static List<Worksheet> GetAllWorksheets(SpreadsheetDocument spreadsheet)
{
var worksheets = new List<Worksheet>();
var worksheetParts = ExcelSpreadsheet.GetAllWorksheetParts(spreadsheet);
foreach (WorksheetPart itemWorksheetPart in worksheetParts)
{
worksheets.Add(itemWorksheetPart.Worksheet);
}
return worksheets;
}
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;
}
public void ImportDataSet(SpreadsheetDocument package, DataSet dataset)
{
for (int i = 0; i < dataset.Tables.Count; i++) {
System.Data.DataTable targetTable = dataset.Tables[i];
string sheetName = string.IsNullOrEmpty(targetTable.TableName) ? "sheet" + i : targetTable.TableName;
ImportDataTable(package, targetTable, sheetName);
}
}
private Column CreateColumn(SpreadsheetDocument doc, SheetData sheetData, int colIndex)
{
var column = new Column
{
Min = (uint) (colIndex + 1),
Max = (uint) (colIndex + 1),
Width = CalculateColumnWidth(doc, sheetData, colIndex),
CustomWidth = true
};
return column;
}
private CellFormat GetCellFormat(SpreadsheetDocument doc, Cell cell)
{
var index = (int) cell.StyleIndex.Value;
var cellFormat = doc.WorkbookPart
.WorkbookStylesPart
.Stylesheet
.CellFormats
.Elements<CellFormat>()
.ElementAt(index);
return cellFormat;
}
public static List<WorksheetPart> GetAllWorksheetParts(SpreadsheetDocument spreadsheet)
{
var worksheetParts = new List<WorksheetPart>();
var sheets = ExcelSpreadsheet.GetSheets(spreadsheet);
foreach (Sheet itemSheet in sheets)
{
String SheetID = itemSheet.Id;
WorksheetPart wsPart = (WorksheetPart)spreadsheet.WorkbookPart.GetPartById(SheetID);
worksheetParts.Add(wsPart);
}
return worksheetParts;
}
// 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 double CalculateColumnWidth(SpreadsheetDocument doc, SheetData sheetData, int colIndex)
{
// See http://refactorsaurusrex.com/how-to-get-the-custom-format-string-for-an-excelopenxml-cell/
var allCellWidth = from cell in GetColumnCells(sheetData, colIndex)
let cellFormat = GetCellFormat(doc, cell)
let font = GetFont(doc, cellFormat)
let fontName = font.FontName.Val
let fontSize = int.Parse(font.FontSize.Val)
let width = CalculateTextWidth(fontName, fontSize, cell.InnerText)
select width;
return allCellWidth.Max();
}
public static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
if (cell.CellValue == null) {
return "";
}
string value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) {
return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
} else {
return value;
}
}
public static bool AddSharedString(SpreadsheetDocument spreadsheet, string stringItem, bool save = true)
{
SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
if (0 == sharedStringTable.Where(item => item.InnerText == stringItem).Count())
{
sharedStringTable.AppendChild(new SharedStringItem(new Text(stringItem)));
if (save)
{
sharedStringTable.Save();
}
}
return true;
}
private void AddColumns(SpreadsheetDocument doc, string sheetName)
{
var sheetData = doc.GetSheetDataByName(sheetName);
var headerRow = sheetData.Descendants<Row>().FirstOrDefault();
if (headerRow == null) return;
var colCount = headerRow.Descendants<Cell>().Count();
var columnRange = Enumerable.Range(0, colCount).Select(i => CreateColumn(doc, sheetData, i));
var columns = new Columns(columnRange);
var sheet = doc.GetWorksheetByName(sheetName);
sheet.InsertAt(columns, 0);
}
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();
//}
}
}
private void CheckForErrors()
{
for (int i = 0; i < 3; i++)
{
if (pathes[i] == null || pathes[i].Count == 0)
{
criticalErrors += "Не выбраны все необходимые файлы" + Environment.NewLine;
return;
}
}
for (int i = 0; i < 4; i += 3)
{
foreach (string s in pathes[i])
{
if (Path.GetExtension(s) != ".csv" && Path.GetExtension(s) != ".xlsx")
{
criticalErrors += "Файл ексопрт линков имеет некорректное расширение, измените путь на формат .csv или .xlsx" + Environment.NewLine;
return;
}
}
}
foreach (string s in pathes[1])
{
if (Path.GetExtension(s) != ".xlsx" && Path.GetExtension(s) != ".csv")
{
criticalErrors += "Файл PD имеет некорректное расширение, измените путь на формат .xlsx или .csv" + Environment.NewLine;
return;
}
}
foreach (string s in pathes[2])
{
if (Path.GetExtension(s) != ".xlsx" && Path.GetExtension(s) != ".csv")
{
criticalErrors += "Файл ID имеет некорректное расширение, измените путь на формат .csv или .xlsx" + Environment.NewLine;
return;
}
}
foreach (string path in pathes[1])
{
if (Path.GetExtension(path) == ".xlsx")
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(path, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
if (workbookPart.Workbook.Sheets.Count() < 2)
{
criticalErrors += "В файле продукт даты нет необходимых листов" + Environment.NewLine;
return;
}
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().ElementAt(0);
Worksheet worksheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
SheetData sheetData = worksheet.Elements <SheetData>().First();
int headerColumnCount = sheetData.ElementAt(0).ChildElements.Count;
if (headerColumnCount != 11)
{
criticalErrors += "Некорректное количество колонок на первом листе продукт даты" + Environment.NewLine;
return;
}
Sheet sheet2 = workbookPart.Workbook.Descendants <Sheet>().ElementAt(1);
Worksheet worksheet2 = ((WorksheetPart)workbookPart.GetPartById(sheet2.Id)).Worksheet;
SheetData sheetData2 = worksheet2.Elements <SheetData>().First();
int headerColumnCount2 = sheetData2.ElementAt(0).ChildElements.Count;
if (headerColumnCount2 != 5)
{
criticalErrors += "Некорректное количество колонок на втором листе продукт даты" + Environment.NewLine;
return;
}
if (criticalErrors != null)
{
return;
}
}
for (int i = 1; i < fileReader.PData.PDData1.Count; i++)
{
if (fileReader.PData.PDData1[i][0] == "")
{
criticalErrors += "Пустое значение на первом листе продукт даты в колонке Brand, строка " + i + Environment.NewLine;
}
if (fileReader.PData.PDData1[i][1] == "")
{
criticalErrors += "Пустое значение на первом листе продукт даты в колонке SKU, строка " + i + Environment.NewLine;
}
if (fileReader.PData.PDData1[i][5] == "" || fileReader.PData.PDData1[i][6] == "")
{
criticalErrors += "Пустое значение на первом листе продукт даты в колонках CategoryName or SubtypeName, строка " + i + Environment.NewLine;
}
}
if (criticalErrors != null)
{
return;
}
}
}
HashSet <string> differentRegistrBrand = new HashSet <string>();
for (int i = 1; i < fileReader.PData.PDData1.Count - 2; i++)
{
if (fileReader.PData.PDData1[i][0].ToLower() == fileReader.PData.PDData1[i + 1][0].ToLower() &&
fileReader.PData.PDData1[i][0] != fileReader.PData.PDData1[i + 1][0])
{
differentRegistrBrand.Add(fileReader.PData.PDData1[i][0]);
}
}
if (differentRegistrBrand.Count != 0)
{
otherErrors += "Некоторые бренды содержат разный регистр букв, что может привести к ошибкам (отвалится MMY если один и тот же бренд записан с разным регистром букв в фитмент и продукт дате)" +
Environment.NewLine + "Бренды в которых есть такая ошибка: " + Environment.NewLine;
foreach (string s in differentRegistrBrand)
{
otherErrors += s + Environment.NewLine;
}
}
if (skuFromPDCheck == true)
{
List <List <string> > exportLinks = fileReader.ExportLinks.ToList();
int brandKayPosition = exportLinks[0].Count - 1;
int pDataBrandKayPosition = fileReader.PData.PDData1[0].Count - 1;
HashSet <string> allSKU = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
for (int i = 1; i < exportLinks.Count; i++)
{
allSKU.Add(exportLinks[i][brandKayPosition]);
}
HashSet <string> missingSKU = new HashSet <string>();
for (int i = 1; i < fileReader.PData.PDData1.Count - 1; i++)
{
if (!allSKU.Contains(fileReader.PData.PDData1[i][pDataBrandKayPosition]))
{
missingSKU.Add(fileReader.PData.PDData1[i][1] + " - " + fileReader.PData.PDData1[i][0]);
}
}
if (missingSKU.Count != 0)
{
otherErrors += "Согласно выбраному чекбоксу собрать финиш файл по SKU указаным в продукт дате." + Environment.NewLine + "Не все ску были залиты на сайт, ску которые есть в файле пд и нет в експорт линках:" + Environment.NewLine;
foreach (string s in missingSKU)
{
otherErrors += s + Environment.NewLine;
}
}
}
}
public static DataTable GetSheet(string filename, string sheetName, int jumpRowIndex = 1)
{
DataTable dt = new DataTable();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename, false))
{
WorkbookPart wbPart = document.WorkbookPart;
//通过sheet名查找 sheet页
Sheet sheet = wbPart
.Workbook
.Descendants <Sheet>()
.FirstOrDefault(s => s.Name == sheetName);
if (sheet == null)
{
throw new ArgumentException("未能找到" + sheetName + " sheet 页");
}
//获取Excel中共享表
SharedStringTablePart sharedStringTablePart = wbPart
.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
SharedStringTable sharedStringTable = null;
if (sharedStringTablePart != null)
{
sharedStringTable = sharedStringTablePart.SharedStringTable;
}
#region 构建datatable
//添加talbe列,返回列数
Func <Row, int> addTabColumn = (r) =>
{
//遍历单元格
foreach (Cell c in r.Elements <Cell>())
{
dt.Columns.Add(GetCellVal(c, sharedStringTable));
}
return(dt.Columns.Count);
};
//添加行
Action <Row> addTabRow = (r) =>
{
DataRow dr = dt.NewRow();
int colIndex = 0;
int colCount = dt.Columns.Count;
//遍历单元格
foreach (Cell c in r.Elements <Cell>())
{
if (colIndex >= colCount)
{
break;
}
dr[colIndex++] = GetCellVal(c, sharedStringTable);
}
dt.Rows.Add(dr);
};
#endregion
//通过 sheet.id 查找 WorksheetPart
WorksheetPart worksheetPart
= wbPart.GetPartById(sheet.Id) as WorksheetPart;
//查找 sheetdata
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
//遍历行
foreach (Row r in sheetData.Elements <Row>())
{
//构建table列
if (r.RowIndex == jumpRowIndex)
{
addTabColumn(r);
continue;
}
//构建table行
addTabRow(r);
}
}
return(dt);
}
public async Task <IActionResult> GeneratePayratesFile(UnitTutorsViewModel model)
{
// set file name
var fileName = "StaffPayrates_" + model.UnitCode + ".xlsx";
var filePath = Path.Combine(_hostingEnvironment.WebRootPath, fileName);
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
// create woorkbookpart
WorkbookPart workbookPart = spreadSheetDocument.AddWorkbookPart();
spreadSheetDocument.WorkbookPart.Workbook = new Workbook();
spreadSheetDocument.WorkbookPart.Workbook.Sheets = new Sheets();
// styles part
WorkbookStylesPart stylesPart = spreadSheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
//create worksheetPart Teaching events
WorksheetPart worksheetPartTeachingEvents = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetDataTeachingEvents = new SheetData();
worksheetPartTeachingEvents.Worksheet = new Worksheet();//sheetDataTeachingEvents);
// set column widths
Columns colsTeachingEvents = TeachingEventsColumns();
worksheetPartTeachingEvents.Worksheet.Append(colsTeachingEvents);
worksheetPartTeachingEvents.Worksheet.Append(sheetDataTeachingEvents);
// create merge cells for O1-T1
MergeCells mergeCells = new MergeCells();
mergeCells.Append(new MergeCell()
{
Reference = new StringValue("O1:T1")
});
worksheetPartTeachingEvents.Worksheet.Append(mergeCells);
// create sheet
Sheets sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>();
Sheet sheetTeachingEvents = new Sheet()
{
Id = spreadSheetDocument.WorkbookPart.GetIdOfPart(worksheetPartTeachingEvents),
SheetId = 1,
Name = "1. Teaching Events"
};
sheets.Append(sheetTeachingEvents);
// create worksheetPart New or Edited Staff Details
WorksheetPart worksheetPartNewStaff = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetDataNewStaff = new SheetData();
worksheetPartNewStaff.Worksheet = new Worksheet();
Columns colsNewStaff = NewStaffColumns();
worksheetPartNewStaff.Worksheet.Append(colsNewStaff);
worksheetPartNewStaff.Worksheet.Append(sheetDataNewStaff);
// create merge cells for O1-T1
MergeCells mergeCellsNewStaff = new MergeCells();
mergeCellsNewStaff.Append(new MergeCell()
{
Reference = new StringValue("A1:G1")
});
worksheetPartNewStaff.Worksheet.Append(mergeCellsNewStaff);
// create sheet
Sheet sheetNewStaff = new Sheet()
{
Id = spreadSheetDocument.WorkbookPart.GetIdOfPart(worksheetPartNewStaff),
SheetId = 2,
Name = "2. New or Edited Staff Details"
};
sheets.Append(sheetNewStaff);
// header row Teaching Events
Row headers = new Row();
// column A
headers.Append(StringCell("Subject Code", 3));
// column B
headers.Append(StringCell("Class Type", 3));
// column C (blank column), need to change format
headers.Append(EmptyCell(6));
// column D
headers.Append(StringCell("Day of week", 3));
// column E
headers.Append(StringCell("Start Time", 3));
// column F
headers.Append(StringCell("Duration", 3));
// column G (blank column)
headers.Append(EmptyCell(6));
// column H
headers.Append(StringCell("Staff Name", 3));
// column I (blank column)
headers.Append(EmptyCell(6));
// column J
headers.Append(StringCell("Weeks", 3));
// column K
headers.Append(StringCell("Pay rate", 3));// payrate code
// column L (blank column)
headers.Append(EmptyCell(6));
// column M
headers.Append(StringCell("Staff Status", 3));
// column N (blank column)
headers.Append(EmptyCell(6));
// column O
headers.Append(StringCell("FACULTY SUPPORT STAFF USE ONLY - PLEASE REFRAIN FROM AMENDING COLUMNS O-T", 7));
// add the headers to the sheet
sheetDataTeachingEvents.AppendChild(headers);
List <int> blackCol = new List <int> {
3, 7, 9, 12, 14
};
// Row 2 displays headers for faculty staff use
Row row2 = new Row();
for (int i = 0; i < 14; i++) // column A-N
{
if (blackCol.Contains(i + 1))
{
row2.Append(EmptyCell(6));
}
else
{
row2.Append(EmptyCell(3));
}
}
// column O
row2.Append(StringCell("Pay Rate", 1));
// column P
row2.Append(StringCell("No. of sessions", 1));
// column Q
row2.Append(StringCell("Hours", 1));
// column R
row2.Append(StringCell("Cost", 1));
// column S
row2.Append(StringCell("Cost inc. on-costs", 1));
// column T
row2.Append(StringCell("Notes/Comments", 1));
// add the row to the sheet
sheetDataTeachingEvents.AppendChild(row2);
// warning row for NewStaff sheet
Row warningRow = new Row();
warningRow.Append(StringCell("**ONLY COMPLETE THIS SECTION FOR NEW SESSIONAL STAFF WHO ARE NOT ON SWINBURNE'S PAYROLL", 13));
sheetDataNewStaff.AppendChild(warningRow);
// Header data for NewStaff sheet
Row headersNewStaff = new Row();
// Column A
headersNewStaff.Append(StringCell("Surname", 4));
// Column B
headersNewStaff.Append(StringCell("FirstName", 4));
// Column C
headersNewStaff.Append(StringCell("Email", 4));
// Column D
headersNewStaff.Append(StringCell("Address", 4));
// Column E
headersNewStaff.Append(StringCell("Suburb", 4));
// Column F
headersNewStaff.Append(StringCell("Post Code", 4));
// Column G
headersNewStaff.Append(StringCell("Home Phone", 4));
// Column H
headersNewStaff.Append(StringCell("Work Phone", 4));
// Column I
headersNewStaff.Append(StringCell("Mobile Phone", 4));
sheetDataNewStaff.AppendChild(headersNewStaff);
foreach (TutorPayrateViewModel tutor in model.Tutors.Values)
{
// get weeks that tutor is teaching
List <int> teachingWeeks = new List <int>();
foreach (var kv in tutor.Weeks)
{
if (kv.Value)
{
teachingWeeks.Add(kv.Key);
}
}
Row row = new Row();
// column A
row.Append(StringCell(model.UnitCode, 2));
// column B
row.Append(StringCell(tutor.ClassType, 2));
// column C (blank column)
row.Append(EmptyCell(6));
// column D
row.Append(StringCell(tutor.ClassDayOfWeek, 2));
// column E
row.Append(StringCell(tutor.ClassStartTime.ToString(@"hh\:mm"), 2));
// column F
row.Append(NumberCell(tutor.ClassDuration.TotalMinutes.ToString(), 2));
// column G (blank column)
row.Append(EmptyCell(6));
// column H
row.Append(StringCell(tutor.TutorFullName, 2));
// column I (blank column)
row.Append(EmptyCell(6));
// column J
row.Append(StringCell(string.Join(',', teachingWeeks.ToArray()), 2));
// column K
row.Append(StringCell(tutor.PayrateCode, 2));
// column L (blank column)
row.Append(EmptyCell(6));
// column M
row.Append(StringCell(tutor.StaffStatus, 2));
// column N (blank column)
row.Append(EmptyCell(6));
// column O
Payrate payrate = _context.Payrate.Where(c => c.Code == tutor.PayrateCode).FirstOrDefault();
row.Append(NumberCell(payrate.Rate.ToString(), 11));
// add formating to next 5 rows
for (int i = 0; i < 5; i++)
{
row.Append(EmptyCell(8));
}
sheetDataTeachingEvents.AppendChild(row);
// new staff
if (tutor.NewStaff)
{
Row rowNewStaff = new Row();
// Column A
rowNewStaff.Append(StringCell(tutor.TutorLastName));
// Column B
rowNewStaff.Append(StringCell(tutor.TutorFirstName));
// Column C
rowNewStaff.Append(StringCell(tutor.TutorEmail));
// Column D
rowNewStaff.Append(StringCell(tutor.TutorAddress));
// Column E
rowNewStaff.Append(StringCell(tutor.TutorSuburb));
// Column F
rowNewStaff.Append(StringCell(tutor.TutorPostCode));
// Column G
rowNewStaff.Append(EmptyCell());
// Column H
rowNewStaff.Append(EmptyCell());
// Column I
rowNewStaff.Append(StringCell(tutor.TutorMobileNumber));
sheetDataNewStaff.AppendChild(rowNewStaff);
}
}
// empty row between staff details and total stuff
Row emptyRow = new Row();
sheetDataTeachingEvents.AppendChild(emptyRow);
Row TotalRow = new Row();
for (int i = 0; i < 14; i++) // column A-N
{
TotalRow.Append(EmptyCell());
}
// column O
TotalRow.Append(StringCell("TOTAL", 9));
// column P
Cell noSessions = NumberCell("0.00", 10);
int totalStaff = model.Tutors.Count;
CellFormula noSessionsFormula = new CellFormula();
noSessionsFormula.Text = "SUM(P3:P" + (3 + totalStaff - 1) + ")";
noSessions.CellFormula = noSessionsFormula;
TotalRow.Append(noSessions);
// column Q
Cell hours = NumberCell("0.00", 10);
CellFormula hoursFormula = new CellFormula();
hoursFormula.Text = "SUM(Q3:Q" + (3 + totalStaff - 1) + ")";
hours.CellFormula = hoursFormula;
TotalRow.Append(hours);
// column R
Cell cost = NumberCell("0.00", 12);
CellFormula costFormula = new CellFormula();
costFormula.Text = "SUM(R3:R" + (3 + totalStaff - 1) + ")";
cost.CellFormula = costFormula;
TotalRow.Append(cost);
// column S
Cell costInc = NumberCell("0.00", 12);
CellFormula costIncFormula = new CellFormula();
costIncFormula.Text = "SUM(S3:S" + (3 + totalStaff - 1) + ")";
costInc.CellFormula = costIncFormula;
TotalRow.Append(costInc);
sheetDataTeachingEvents.AppendChild(TotalRow);
}
// Download the created file
if (fileName != null)
{
var memory = new MemoryStream();
using (var stream = new FileStream(filePath, FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
System.IO.File.Delete(filePath);
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
return(RedirectToAction(nameof(Index)));
}
Object ParseFileImpl(Stream stream, ITableDescription table)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
var rv = new List <ExpandoObject>();
using (var doc = SpreadsheetDocument.Open(stream, isEditable: false))
{
var workBookPart = doc.WorkbookPart;
var workBook = workBookPart.Workbook;
var sheet = workBook.Descendants <Sheet>().First();
if (sheet == null)
{
throw new InteropException($"The workbook does not have a sheet");
}
var workSheetPart = (WorksheetPart)workBookPart.GetPartById(sheet.Id);
var sharedStringPart = workBookPart.SharedStringTablePart;
var sharedStringTable = sharedStringPart.SharedStringTable;
var rows = workSheetPart.Worksheet.Descendants <Row>().ToList();
if (rows == null)
{
throw new InteropException($"The sheet does not have a rows");
}
var hdr = rows[0];
List <String> columns = new List <String>();
var hdrCells = hdr.Elements <Cell>().ToList();
for (var ci = 0; ci < hdrCells.Count; ci++)
{
var c = hdrCells[ci];
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
Int32 ssid = Int32.Parse(c.CellValue.Text);
String str = sharedStringTable.ChildElements[ssid].InnerText;
columns.Add(str);
}
}
for (var ri = 1 /*1!*/; ri < rows.Count; ri++)
{
var r = rows[ri];
var dataRow = table.NewRow();
var cells = r.Elements <Cell>().ToList();
for (var ci = 0; ci < cells.Count; ci++)
{
var c = cells[ci];
var colIndex = ToIndex(c.CellReference) - 1;
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
Int32 ssid = Int32.Parse(c.CellValue.Text);
String str = sharedStringTable.ChildElements[ssid].InnerText;
table.SetValue(dataRow, columns[colIndex], str);
}
else if (c.CellValue != null)
{
table.SetValue(dataRow, columns[colIndex], c.CellValue.Text);
}
}
}
}
return(table.ToObject());
}
/// <summary>
/// Данная функция является точкой входа.
/// <summary>
public void Execute(Context context)
{
SpreadsheetDocument spreadsheetDocument = null;
// Проверка, существует ли файл Excel по пути, указанному в контекстной переменной.
// На самом деле не обязательно. Если нужно всегда создавать новый (с перезаписью),
// то можно всегда использовать SpreadsheetDocument.Create()
// Но в качестве примера рассмотрим ситуацию с изменением уже созданного файла Excel
bool excelExists = File.Exists(context.ExcelFilePath);
if (excelExists)
{
// При существующем файле открываем
spreadsheetDocument = SpreadsheetDocument.Open(context.ExcelFilePath, true);
}
else
{
// Если файл не существует, то создаем
spreadsheetDocument = SpreadsheetDocument.Create(context.ExcelFilePath, SpreadsheetDocumentType.Workbook);
}
// Workbook - книга (по другому можно сказать), является элементом верхнего уровня,
// которая содержит несколько таблиц (или другие встречаемые термины: страницы, листы)) и другие атрибуты.
// Первым делом нужно получить его или создать новый.
WorkbookPart workbookPart = excelExists ? spreadsheetDocument.WorkbookPart : spreadsheetDocument.AddWorkbookPart();
if (!excelExists || workbookPart == null)
{
workbookPart.Workbook = new Workbook();
}
Sheet sheet = null;
// Worksheet - рабочий лист. Необходимо найти по имени, а не первый,
// который по сути будет последним активным при сохранении Excel.
WorksheetPart 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)
{
// Нужно задать верный id для нового листа, после последнего
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);
// Далее можно настроить столбцы, но необязательно
// Если не настраивать, то будет стандартный вид.
Columns columns = worksheetPart.Worksheet.GetFirstChild <Columns>();
bool needToInsertColumns = false;
if (columns == null)
{
columns = new Columns();
needToInsertColumns = true;
}
// Для примера создадим столбец с настроенной длиной
Column column1 = new Column()
{
Min = 1U, Max = 1U, Width = 20D, CustomWidth = true
};
columns.Append(column1);
if (needToInsertColumns)
{
worksheetPart.Worksheet.InsertAt(columns, 0);
}
}
// Страницу можно также получить по id, например, первую так:
// sheet = (Sheet)workbookPart.Workbook.Sheets.ChildElements.GetItem(0);
// Получим данные листа
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
// Далее нужно по необходимости. Если предпологается, что всегда будет создаваться новый,
// то можно просто установить rowIndex = 1U (именно 1, не 0).
// Если всегда предполагается добавление новых строк с существующим,
// то нужно установить значение rowIndex, относительно количества существующих строк:
var rows = sheetData.Elements <Row>();
uint rowIndex = excelExists ? (uint)rows.Count() + 1U : 1U;
// Создание строки с индексом
Row row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
// Пример новой ячейки с текстом:
Cell newCell1 = InsertCellInWorksheet("A", rowIndex, worksheetPart);
newCell1.CellValue = new CellValue("Какой-то текст");
newCell1.DataType = new EnumValue <CellValues>(CellValues.String);
// Инкрементируем индекс
rowIndex++;
// Пример новый ячейки с числом
Cell newCell2 = InsertCellInWorksheet("A", rowIndex, worksheetPart);
newCell2.CellValue = new CellValue("12345");
newCell2.DataType = new EnumValue <CellValues>(CellValues.Number);
// Не забываем закрывать, если не использовали блок using:
spreadsheetDocument.Close();
}
public static void Create(string filename, DataSet ds)
{
Regex regex = new Regex(@"^(-?\d+)(\.\d+)?$");
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
#region 创建多个 sheet 页
//创建多个sheet
for (int s = 0; s < ds.Tables.Count; s++)
{
DataTable dt = ds.Tables[s];
var tname = dt.TableName;
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();
//创建 sheet 页
Sheet sheet = new Sheet()
{
//页面关联的 WorksheetPart
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = UInt32Value.FromUInt32((uint)s + 1),
Name = tname
};
sheets.Append(sheet);
#region 创建sheet 行
Row row;
uint rowIndex = 1;
//添加表头
row = new Row()
{
RowIndex = UInt32Value.FromUInt32(rowIndex++)
};
sheetData.Append(row);
for (int i = 0; i < dt.Columns.Count; i++)
{
Cell newCell = new Cell();
newCell.CellValue = new CellValue(dt.Columns[i].ColumnName);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.Append(newCell);
}
//添加内容
object val = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
row = new Row()
{
RowIndex = UInt32Value.FromUInt32(rowIndex++)
};
sheetData.Append(row);
for (int j = 0; j < dt.Columns.Count; j++)
{
Cell newCell = new Cell();
val = dt.Rows[i][j];
var str = (val?.ToString() ?? "").Trim();
if (str.Length == 0 || regex.IsMatch(str))
{
newCell.CellValue = new CellValue(str);
newCell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else
{
newCell.CellValue = new CellValue(val.ToString());
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
}
row.Append(newCell);
}
}
#endregion
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
}
#endregion
workbook.Append(sheets);
workbookpart.Workbook = workbook;
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
public static void CloseSpreadSheet(SpreadsheetDocument ss)
{
ss.Close();
}
/// <summary>
/// 读取Excel中数据，包括样式
/// </summary>
/// <param name="excel"></param>
/// <param name="sheetName"></param>
/// <param name="file"></param>
/// <returns></returns>
public static List <SheetDataList> ReadExcelDetailTest(SpreadsheetDocument excel, List <string> sheetName, string file)
{
try
{
#region //获取样式设置
Stylesheet styleSheet = excel.WorkbookPart.WorkbookStylesPart.Stylesheet;
//获取Excel文件主题色
ThemePart themPart = excel.WorkbookPart.ThemePart;
var themColor = ThemeColor.GetThemeColorList(themPart);
#region 样式列表
CellFormats cellFormats = styleSheet.CellFormats;
List <CellFormatsList> cellFormatsList = new List <CellFormatsList>();
int index = 0;
foreach (CellFormat cell in cellFormats.ChildElements)
{
if (cell != null)
{
CellFormatsList cfl = new CellFormatsList();
cfl.styleIndex = index;
if (cell.NumberFormatId != null)
{
cfl.numFmtId = int.Parse(cell.NumberFormatId);
}
if (cell.FontId != null)
{
cfl.fontId = int.Parse(cell.FontId);
}
if (cell.FillId != null)
{
cfl.fillId = int.Parse(cell.FillId);
}
if (cell.BorderId != null)
{
cfl.borderId = int.Parse(cell.BorderId);
}
if (cell.ApplyAlignment != null)
{
cfl.applyAlignment = int.Parse(cell.ApplyAlignment);
}
if (cell.ApplyBorder != null)
{
cfl.applyBorder = int.Parse(cell.ApplyBorder);
}
if (cell.ApplyFont != null)
{
cfl.applyFont = int.Parse(cell.ApplyFont);
}
if (cell.ApplyNumberFormat != null)
{
cfl.applyNumberFormat = int.Parse(cell.ApplyNumberFormat);
}
if (cell.Alignment != null)
{
string ver = cell.Alignment.Vertical;
string hor = cell.Alignment.Horizontal;
string wra = cell.Alignment.WrapText;
if (!string.IsNullOrEmpty(ver))
{
if (ver == "center")
{
cfl.vertical = "htMiddle";
}
else
{
cfl.vertical = "ht" + ver.Substring(0, 1).ToUpper() + ver.Substring(1, ver.Length - 1);
}
}
else
{
cfl.vertical = "htBottom";
}
if (!string.IsNullOrEmpty(hor))
{
cfl.horizontal = "ht" + hor.Substring(0, 1).ToUpper() + hor.Substring(1, hor.Length - 1);
}
else
{
cfl.horizontal = "htLeft";
}
cfl.wraptext = wra;
}
cellFormatsList.Add(cfl);
index++;
}
}
#endregion
#region 数据类型列表
NumberingFormats numberFormats = styleSheet.NumberingFormats;
List <NumFmtsList> numFmtList = new List <NumFmtsList>();
if (numberFormats != null)
{
foreach (NumberingFormat cell in numberFormats.ChildElements)
{
NumFmtsList nfl = new NumFmtsList();
if (cell.NumberFormatId != null)
{
nfl.numFmtId = (int)cell.NumberFormatId.Value;
}
if (cell.FormatCode != null)
{
nfl.formatCode = cell.FormatCode.Value;
}
numFmtList.Add(nfl);
}
}
#endregion
#region 字体样式
Fonts fonts = styleSheet.Fonts;
List <FontsList> fontsList = new List <FontsList>();
foreach (Font cell in fonts.ChildElements)
{
FontsList fl = new FontsList();
if (cell.FontSize != null)
{
fl.fontsize = cell.FontSize.Val + "px";
}
if (cell.FontName != null)
{
fl.fontname = cell.FontName.Val;
}
if (cell.Color != null)
{
if (cell.Color.Rgb != null && !string.IsNullOrEmpty(cell.Color.Rgb.ToString()))
{
fl.color = "#" + cell.Color.Rgb.ToString().Substring(2, 6);
}
}
if (cell.Italic != null)
{
fl.italic = "italic";
}
if (cell.Bold != null)
{
fl.bold = "bold";
}
if (cell.Underline != null)
{
fl.underline = "underline";
}
fontsList.Add(fl);
}
#endregion
#region 填充色样式
Fills fills = styleSheet.Fills;
List <FillsList> fillsList = new List <FillsList>();
foreach (Fill cell in fills.ChildElements)
{
FillsList fl = new FillsList();
if (cell.PatternFill != null)
{
fl.patternType = cell.PatternFill.PatternType;
if (cell.PatternFill.ForegroundColor != null)
{
if (cell.PatternFill.ForegroundColor.Rgb != null)
{
fl.fgColor = "#" + cell.PatternFill.ForegroundColor.Rgb.ToString().Substring(2, 6);
}
if (cell.PatternFill.ForegroundColor.Theme != null)
{
UInt32Value themeIndex = cell.PatternFill.ForegroundColor.Theme;
DoubleValue tint = cell.PatternFill.ForegroundColor.Tint;
if (tint != null)
{
var newColor = ThemeColor.ThemColorDeal(themeIndex, tint, themColor[themeIndex]);
fl.fgColor = "#" + newColor.Name.Substring(2, 6);
fl.fgColor = "#" + newColor.Name.Substring(2, 6);
}
else
{
fl.fgColor = "#" + themColor[themeIndex];
fl.fgColor = "#" + themColor[themeIndex];
}
}
}
}
fillsList.Add(fl);
}
#endregion
#region 边框样式
Borders borders = styleSheet.Borders;
List <BordersList> bordersList = new List <BordersList>();
var defaultBorderStyle = "1px solid #000";
foreach (Border cell in borders.ChildElements)
{
BordersList bl = new BordersList();
if (cell.LeftBorder != null)
{
if (cell.LeftBorder.Style != null)
{
bl.left = defaultBorderStyle;
}
}
if (cell.RightBorder != null)
{
if (cell.RightBorder.Style != null)
{
bl.right = defaultBorderStyle;
}
}
if (cell.TopBorder != null)
{
if (cell.TopBorder.Style != null)
{
bl.top = defaultBorderStyle;
}
}
if (cell.BottomBorder != null)
{
if (cell.BottomBorder.Style != null)
{
bl.bottom = defaultBorderStyle;
}
}
if (cell.DiagonalBorder != null)
{
if (cell.DiagonalBorder.Style != null)
{
bl.diagonal = cell.DiagonalBorder.Style;
}
}
bordersList.Add(bl);
}
#endregion
#endregion
List <SheetDataList> listSDL = new List <SheetDataList>();
List <PictureInfo> pictures = null;
//获取多个Sheet数据和样式
for (int i = 0; i < sheetName.Count; i++)
{
//总行数和总列数
int RowCount = 0, ColumnCount = 0;
SheetDataList sdl = new SheetDataList();
//获取一个工作表的数据
WorksheetPart worksheet = ExcelHelper.GetWorksheetPartByName(excel, sheetName[i]);
#region //批注
WorksheetCommentsPart comments = worksheet.WorksheetCommentsPart;
List <CommentCellsList> commentLists = new List <CommentCellsList>();
if (comments != null)
{
CommentList commentList = (CommentList)comments.Comments.ChildElements[1];
//批注列表
foreach (Comment comment in commentList.ChildElements)
{
CommentCellsList ccl = new CommentCellsList();
//坐标
var cell = GetCellXY(comment.Reference).Split('_');
var columnRow = int.Parse(cell[0].ToString()) - 1;
var columnCol = GetColumnIndex(cell[1]);
//批注内容
var commentVal = comment.InnerText;
ccl.row = columnRow;
ccl.col = columnCol;
ccl.comment = comment.InnerText;
//var commentCell = "{\"Row\":\""+ columnRow + "\",\"Col\":\"" + columnCol + ",\"Comment\":\"" + commentVal + "\"}";
commentLists.Add(ccl);
}
}
sdl.Comments = commentLists;
#endregion
#region //获取合并单元格
IEnumerable <MergeCells> mergeCells = worksheet.Worksheet.Elements <MergeCells>();
List <MergeCellsList> mergeCellList = new List <MergeCellsList>();
if (mergeCells.Count() > 0)
{
for (int k = 0; k < mergeCells.First().ChildElements.Count; k++)
{
MergeCell mergeCell = (MergeCell)mergeCells.First().ChildElements[k];
var reference = mergeCell.Reference.ToString().Split(':');
var startCell = GetCellXY(reference[0]).Split('_');
var endCell = GetCellXY(reference[1]).Split('_');
MergeCellsList mcl = new MergeCellsList();
mcl.row = int.Parse(startCell[0]) - 1;
mcl.rowspan = int.Parse(endCell[0]) - int.Parse(startCell[0]) + 1;
mcl.col = GetColumnIndex(startCell[1]);
mcl.colspan = GetColumnIndex(endCell[1]) - mcl.col + 1;
//mcl.reference = mergeCell.Reference.ToString();
mergeCellList.Add(mcl);
}
}
sdl.MergeCells = mergeCellList;
#endregion
#region //读取图片
DrawingsPart drawingPart = worksheet.GetPartsOfType <DrawingsPart>().ToList().FirstOrDefault();
pictures = new List <PictureInfo>();
if (drawingPart != null)
{
int tempIndex = 1;
foreach (var part in drawingPart.Parts)
{
PictureInfo pic = new PictureInfo();
ImagePart imgPart = (ImagePart)part.OpenXmlPart;
System.Drawing.Image img1 = System.Drawing.Image.FromStream(imgPart.GetStream());
var newFilename = Guid.NewGuid().ToString("N") + ".png";
string[] sArray = Regex.Split(file, "UserFile", RegexOptions.IgnoreCase);
string newFilePath = sArray[0] + "_Temp\\" + newFilename;
img1.Save(newFilePath);
//pic.Image = img1;
pic.RefId = part.RelationshipId;//"rId" + imgPart.Uri.ToString().Split('/')[3].Split('.')[0].Substring(5);
pic.ImageUrl = newFilePath;
pic.ImageName = newFilename;
pic.ImgHeight = img1.Height;
pic.ImgWidth = img1.Width;
pictures.Add(pic);
tempIndex++;
}
//获取图片定位
var worksheetDrawings = drawingPart.WorksheetDrawing.Where(c => c.ChildElements.Any
(a => a.GetType().FullName == "DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture")).ToList();
foreach (var worksheetDrawing in worksheetDrawings)
{
if (worksheetDrawing.GetType().FullName ==
"DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor")
{
TwoCellAnchor anchor = (TwoCellAnchor)worksheetDrawing;
DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picDef =
(DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture)
anchor.ChildElements.FirstOrDefault(c => c.GetType().FullName ==
"DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture");
if (picDef != null)
{
var embed = picDef.BlipFill.Blip.Embed;
if (embed != null)
{
var picMapping = pictures.FirstOrDefault(c => c.RefId == embed.InnerText);
picMapping.FromCol = int.Parse(anchor.FromMarker.ColumnId.InnerText);
picMapping.FromRow = int.Parse(anchor.FromMarker.RowId.InnerText);
}
}
// anchor.FromMarker.RowId + anchor.FromMarker.ColumnId
}
}
}
sdl.PictureList = pictures;
#endregion
//读取列宽
IEnumerable <Columns> colsList = worksheet.Worksheet.Elements <Columns>();
#region //读取表格数据
List <SheetDatas> sheetDatas = new List <SheetDatas>();
if (worksheet.Rows().Count() > 0)
{
RowCount = int.Parse((worksheet.Rows().Last()).RowId);
}
foreach (OpenXmlPowerTools.Row row in worksheet.Rows())
{
int TempColumn = 0;
int r = 0;
foreach (OpenXmlPowerTools.Cell cell in row.Cells())
{
int co = 0;
//读取超链接？？？
//读取单元格数据
SheetDatas sheetData = new SheetDatas();
sheetData.RowId = int.Parse(row.RowId) - 1;
sheetData.ColumnId = cell.ColumnIndex;
sheetData.Column = cell.Column;
sheetData.Type = cell.Type;
sheetData.Value = cell.Value;
sheetData.SharedString = cell.SharedString;
sheetData.Formula = cell.Formula;
sheetData.StyleId = cell.Style;
//读取列宽(仅限第一行设置列宽)
if (colsList.Count() > 0 && r == 0)
{
Columns col = colsList.ElementAt <Columns>(0);
foreach (Column c in col.ChildElements)
{
if (c.Max == cell.ColumnIndex)
{
sheetData.Width = c.Width;
break;
}
}
}
//读取行高（仅限第一列设置行高）
if (co == 0)
{
if (row.RowElement.Attribute("ht") != null)
{
sheetData.Height = double.Parse(row.RowElement.Attribute("ht").Value);
}
}
#region 样式赋值
if (sheetData.StyleId != null)
{
CellFormatsList cfl = cellFormatsList[(int)sheetData.StyleId];
//字体样式
sheetData.FontName = fontsList[cfl.fontId].fontname;
sheetData.FontSize = fontsList[cfl.fontId].fontsize;
sheetData.FontColor = fontsList[cfl.fontId].color;
sheetData.FontBold = fontsList[cfl.fontId].bold;
sheetData.Italic = fontsList[cfl.fontId].italic;
sheetData.Underline = fontsList[cfl.fontId].underline;
sheetData.AligmentVertical = cfl.vertical;
sheetData.AligmentHorizontal = cfl.horizontal;
sheetData.WrapText = cfl.wraptext;
//背景色样式
sheetData.FillType = fillsList[cfl.fillId].patternType;
sheetData.FillForegroundColor = fillsList[cfl.fillId].fgColor;
sheetData.FillBackgroundColor = fillsList[cfl.fillId].bgColor;
//边框样式
sheetData.LeftBorder = bordersList[cfl.borderId].left;
sheetData.RightBorder = bordersList[cfl.borderId].right;
sheetData.TopBorder = bordersList[cfl.borderId].top;
sheetData.BottomBorder = bordersList[cfl.borderId].bottom;
sheetData.DiagonalBorder = bordersList[cfl.borderId].diagonal;
}
#endregion
//识别文字格式？？？（日期与数字的区别）
sheetDatas.Add(sheetData);
TempColumn++;
co++;
}
r++;
//计算列数
if (TempColumn > ColumnCount)
{
ColumnCount = TempColumn;
}
}
sdl.SheetData = sheetDatas;
#endregion
sdl.SheetName = sheetName[i];
sdl.SheetId = "sheet" + (i + 1);
sdl.TotalRow = RowCount < 20 ? 20 : RowCount + 1;
sdl.TotalColumn = ColumnCount < 15 ? 15 : ColumnCount + 1;
listSDL.Add(sdl);
}
return(listSDL);
}
catch (Exception ex)
{
throw;
}
}
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();
// to prevent 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;
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string workSheetID = "rId" + worksheetNumber.ToString();
//KD Change
//bool p = false;
//if(p)
//{
//}
string worksheetName = dt.TableName;
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();
// create sheet data
newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
// To set column width in the worksheet. Created by Apt
int numberOfColumns = dt.Columns.Count;
for (int colInx = 1; colInx <= numberOfColumns; colInx++)
{
SetColumnWidth(newWorksheetPart.Worksheet, colInx, 30);
}
//End
newWorksheetPart.Worksheet.Save();
// create the worksheet to workbook relation
if (worksheetNumber == 1)
{
spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
}
spreadsheet.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = (uint)worksheetNumber,
Name = worksheetName.Replace(':', '_').Replace('/', '_')///replacing special characters
});
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Save();
}
/// <summary>
/// Creates the content of the shet (columns, rows, cells)
/// </summary>
/// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
/// <param name="worksheetPart">The worksheetpart for this item</param>
private void InsertTextIntoCells(SpreadsheetDocument spreadsheetDocument, WorksheetPart worksheetPart)
{
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Any())
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
#region Excel headers
const string header1 = "DepartmentID";
int index1 = _sharedResources.InsertSharedStringItem(header1, shareStringPart);
Cell headerCell1 = _sharedResources.InsertCellInWorksheet("A", 1, worksheetPart);
headerCell1.CellValue = new CellValue(index1.ToString(CultureInfo.InvariantCulture));
headerCell1.DataType = new EnumValue<CellValues>(CellValues.SharedString);
const string header2 = "PageTypeID";
int index2 = _sharedResources.InsertSharedStringItem(header2, shareStringPart);
Cell headerCell2 = _sharedResources.InsertCellInWorksheet("B", 1, worksheetPart);
headerCell2.CellValue = new CellValue(index2.ToString(CultureInfo.InvariantCulture));
headerCell2.DataType = new EnumValue<CellValues>(CellValues.SharedString);
const string header3 = "GroupTypeID";
int index3 = _sharedResources.InsertSharedStringItem(header3, shareStringPart);
Cell headerCell3 = _sharedResources.InsertCellInWorksheet("C", 1, worksheetPart);
headerCell3.CellValue = new CellValue(index3.ToString(CultureInfo.InvariantCulture));
headerCell3.DataType = new EnumValue<CellValues>(CellValues.SharedString);
const string header4 = "GroupOrder";
int index4 = _sharedResources.InsertSharedStringItem(header4, shareStringPart);
Cell headerCell4 = _sharedResources.InsertCellInWorksheet("D", 1, worksheetPart);
headerCell4.CellValue = new CellValue(index4.ToString(CultureInfo.InvariantCulture));
headerCell4.DataType = new EnumValue<CellValues>(CellValues.SharedString);
#endregion
#region Insert columns and rows
int columnCount = 1;
uint rowCount = 2;
//_pages = _workspaceVm.PageList;
//_orders = _groupOrder.ktUIGroupOrderList;
//foreach (ktUIGroupOrder order in _orders)
//{
// if (columnCount >= 4)
// {
// columnCount = 1;
// }
// string text1 = order.DepartmentID;
// Cell cell1 =
// _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
// rowCount, worksheetPart);
// cell1.CellValue = new CellValue(text1);
// cell1.DataType = CellValues.Number;
// columnCount++;
// string text2 = order.PageTypeID;
// Cell cell2 =
// _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
// rowCount, worksheetPart);
// cell2.CellValue = new CellValue(text2);
// cell2.DataType = CellValues.Number;
// columnCount++;
// string text3 = order.GroupTypeID;
// Cell cell3 =
// _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
// rowCount, worksheetPart);
// cell3.CellValue = new CellValue(text3);
// cell3.DataType = CellValues.Number;
// columnCount++;
// double text4 = Convert.ToDouble(order.GroupOrder);
// Cell cell4 =
// _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
// rowCount, worksheetPart);
// cell4.CellValue = new CellValue(text4.ToString(CultureInfo.InvariantCulture));
// cell4.DataType = CellValues.Number;
// rowCount++;
//}
//foreach (PageType page in _pages)
//{
// foreach (GroupTypeOrder gtOrder in page.Groups)
// {
// if (_orders.Any(x => x.GroupTypeID != gtOrder.GroupTypeID))
// {
// if (columnCount >= 4)
// {
// columnCount = 1;
// }
// string text1 = gtOrder.DepartmentID; //order.DepartmentID;
// Cell cell1 =
// _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
// rowCount, worksheetPart);
// cell1.CellValue = new CellValue(text1);
// cell1.DataType = CellValues.Number;
// columnCount++;
// string text2 = gtOrder.PageTypeID; //order.PageTypeID;
// Cell cell2 =
// _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
// rowCount, worksheetPart);
// cell2.CellValue = new CellValue(text2);
// cell2.DataType = CellValues.Number;
// columnCount++;
// string text3 = gtOrder.GroupTypeID; //order.GroupTypeID;
// Cell cell3 =
// _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
// rowCount, worksheetPart);
// cell3.CellValue = new CellValue(text3);
// cell3.DataType = CellValues.Number;
// columnCount++;
// double text4 = Convert.ToDouble(gtOrder.GroupOrder); //Convert.ToDouble(order.GroupOrder);
// Cell cell4 =
// _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
// rowCount, worksheetPart);
// cell4.CellValue = new CellValue(text4.ToString(CultureInfo.InvariantCulture));
// cell4.DataType = CellValues.Number;
// rowCount++;
// }
// }
//}
foreach (PageType page in _workspaceVm.PageList)
{
foreach (GroupTypeOrder group in page.GroupTypeOrders)
{
if (columnCount >= 4)
{
columnCount = 1;
}
string text1 = group.DepartmentID;
Cell cell1 =
_sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
rowCount, worksheetPart);
cell1.CellValue = new CellValue(text1);
cell1.DataType = CellValues.Number;
columnCount++;
string text2 = page.PageTypeID;
Cell cell2 =
_sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
rowCount, worksheetPart);
cell2.CellValue = new CellValue(text2);
cell2.DataType = CellValues.Number;
columnCount++;
string text3 = group.GroupTypeID;
Cell cell3 =
_sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
rowCount, worksheetPart);
cell3.CellValue = new CellValue(text3);
cell3.DataType = CellValues.Number;
columnCount++;
double text4 = Convert.ToDouble(group.GroupOrder);
Cell cell4 =
_sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true),
rowCount, worksheetPart);
cell4.CellValue = new CellValue(text4.ToString(CultureInfo.InvariantCulture));
cell4.DataType = CellValues.Number;
rowCount++;
}
}
#endregion
worksheetPart.Worksheet.Save();
}
public void ParseXlsx()
{
try
{
using (Stream stream = File.Open(thrlistPath, FileMode.Open))
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
int t = -1;
int i = 0;
foreach (Row r in sheetData.Elements <Row>().Skip(2))
{
threats.Add(new ThreatModel());
t++;
i = 0;
foreach (Cell c in r.Elements <Cell>().Take(8))
{
switch (i)
{
case 0:
threats[t].ThreatId = Int32.Parse(c.InnerText);
break;
case 1:
int id = Int32.Parse(c.InnerText);
SharedStringItem item = GetSharedStringItemById(workbookPart, id);
threats[t].ThreatName = item.Text.Text;
break;
case 2:
id = Int32.Parse(c.InnerText);
item = GetSharedStringItemById(workbookPart, id);
threats[t].Description = item.Text.Text;
break;
case 3:
id = Int32.Parse(c.InnerText);
item = GetSharedStringItemById(workbookPart, id);
threats[t].Source = item.Text.Text;
break;
case 4:
id = Int32.Parse(c.InnerText);
item = GetSharedStringItemById(workbookPart, id);
threats[t].Target = item.Text.Text;
break;
case 5:
threats[t].ConfidentialityBreach = (c.InnerText == "1") ? true : false;
break;
case 6:
threats[t].IntegrityViolation = (c.InnerText == "1") ? true : false;
break;
case 7:
threats[t].AccessViolation = (c.InnerText == "1") ? true : false;
break;
default:
break;
}
i++;
}
}
}
}
catch (FileFormatException)
{
MessageBox.Show("Your file seems to be corrupted or has wrong format");
}
catch (DirectoryNotFoundException)
{
MessageBox.Show("Directory not found \n App (project) should have 'Data' in the root folder");
}
catch (FileNotFoundException)
{
MessageBox.Show("Missing file or disk fails");
}
catch (IOException)
{
MessageBox.Show("Refresh button needs to rest... Please try again");
}
catch (UnauthorizedAccessException)
{
MessageBox.Show("OS denies acces to file \n" + " Try again and...\n May the Force be with you");
}
catch (Exception)
{
MessageBox.Show("We made a little whoopsie --- Already fixing! \n Please try again");
}
finally
{
updateThreats = threats;
Paginate(threats);
currentPage = 0;
PreviousPage.Visibility = Visibility.Collapsed;
if (Pages.Count == 1)
{
NextPage.Visibility = Visibility.Collapsed;
}
else
{
NextPage.Visibility = Visibility.Visible;
}
Table.ItemsSource = null;
Table.ItemsSource = Pages[0];
RefreshFile.Visibility = Visibility.Visible;
}
}
/// <summary>
/// Edit Slicer element
/// </summary>
/// <param name="stream">Editing target stream</param>
/// <param name="log">Logger</param>
public void EditElements(Stream stream, VerifiableLog log)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Open(stream, true))
{
//Getting of SlicerCacheParts.
SlicerCachePart slicerCachePart1 = GetSlicerCachePart(package.WorkbookPart, Slicer1);
SlicerCachePart slicerCachePart2 = GetSlicerCachePart(package.WorkbookPart, Slicer2);
SlicerCachePart slicerCachePart3 = GetSlicerCachePart(package.WorkbookPart, Slicer3);
X15.TableSlicerCache tableSlicerCache1 = slicerCachePart1.RootElement.Descendants <X15.TableSlicerCache>().Single();
X15.TableSlicerCache tableSlicerCache2 = slicerCachePart2.RootElement.Descendants <X15.TableSlicerCache>().Single();
X15.TableSlicerCache tableSlicerCache3 = slicerCachePart3.RootElement.Descendants <X15.TableSlicerCache>().Single();
//Change TableId attribute value of TableSlicerCache.
tableSlicerCache1.TableId.Value = 2;
log.Pass("Edited TableSlicerCache attribute of TableId.");
//Change Column attribute value.
//Change Column attribute value of TableSlicerCache.
tableSlicerCache1.Column.Value = 2;
log.Pass("Edited TableSlicerCache attribute of Column.");
//Change SortOrder attribute value.
//Change SortOrder attribute value of TableSlicerCache. Set value is Descending.
tableSlicerCache1.SortOrder = X14.TabularSlicerCacheSortOrderValues.Descending;
log.Pass("Edited TableSlicerCache attribute of SortOrder. Set value is Descending.");
//Change SortOrder attribute value of TableSlicerCache. Set value is Ascending.
tableSlicerCache2.SortOrder = X14.TabularSlicerCacheSortOrderValues.Ascending;
log.Pass("Edited TableSlicerCache attribute of SortOrder. Set value is Ascending.");
//Change CustomListSort attribute value.
//Change CustomListSort attribute value of TableSlicerCache. Set value is false.
tableSlicerCache1.CustomListSort = false;
log.Pass("Edited TableSlicerCache attribute of CustomListSort. Set value is false.");
//Change CustomListSort attribute value of TableSlicerCache. Set value is true.
tableSlicerCache2.CustomListSort = true;
log.Pass("Edited TableSlicerCache attribute of CustomListSort. Set value is true.");
//Change CrossFilter attribute value.
//Change CrossFilter attribute value of TableSlicerCache. Set value is None.
tableSlicerCache1.CrossFilter = X14.SlicerCacheCrossFilterValues.None;
log.Pass("Edited TableSlicerCache attribute of CrossFilter. Set value is None.");
//Change CrossFilter attribute value of TableSlicerCache. Set value is ShowItemsWithDataAtTop.
tableSlicerCache2.CrossFilter = X14.SlicerCacheCrossFilterValues.ShowItemsWithDataAtTop;
log.Pass("Edited TableSlicerCache attribute of CrossFilter. Set value is ShowItemsWithDataAtTop.");
//Change CrossFilter attribute value of TableSlicerCache. Set value is ShowItemsWithNoData.
tableSlicerCache3.CrossFilter = X14.SlicerCacheCrossFilterValues.ShowItemsWithNoData;
log.Pass("Edited TableSlicerCache attribute of CrossFilter. Set value is ShowItemsWithNoData.");
X15.TableSlicerCache newTableSlicerCache1 = tableSlicerCache1.Clone() as X15.TableSlicerCache;
X15.TableSlicerCache newTableSlicerCache2 = tableSlicerCache2.Clone() as X15.TableSlicerCache;
X15.TableSlicerCache newTableSlicerCache3 = tableSlicerCache3.Clone() as X15.TableSlicerCache;
//Remove the existing Extension, to set the new Extension.
slicerCachePart1.RootElement.Descendants <SlicerCacheDefinitionExtension>().Single().Remove();
slicerCachePart2.RootElement.Descendants <SlicerCacheDefinitionExtension>().Single().Remove();
slicerCachePart3.RootElement.Descendants <SlicerCacheDefinitionExtension>().Single().Remove();
SlicerCacheDefinitionExtension extension1 = new SlicerCacheDefinitionExtension()
{
Uri = "{2F2917AC-EB37-4324-AD4E-5DD8C200BD13}"
};
extension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension1.Append(newTableSlicerCache1);
slicerCachePart1.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild(extension1);
SlicerCacheDefinitionExtension extension2 = new SlicerCacheDefinitionExtension()
{
Uri = "{2F2917AC-EB37-4324-AD4E-5DD8C200BD13}"
};
extension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension2.Append(newTableSlicerCache2);
slicerCachePart2.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild(extension2);
SlicerCacheDefinitionExtension extension3 = new SlicerCacheDefinitionExtension()
{
Uri = "{2F2917AC-EB37-4324-AD4E-5DD8C200BD13}"
};
extension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension3.Append(newTableSlicerCache3);
slicerCachePart3.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild(extension3);
//Append SlicerCacheHideItemsWithNoData element.
Extension extension = new Extension()
{
Uri = "{470722E0-AACD-4C17-9CDC-17EF765DBC7E}"
};
extension.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension.AppendChild <X15.SlicerCacheHideItemsWithNoData>(new X15.SlicerCacheHideItemsWithNoData());
slicerCachePart2.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild <Extension>(extension);
log.Pass("Added SlicerCacheHideItemsWithNoData element.");
}
}
public string WriteToExcel(ReportDTO reportDTO, string templatePath, string exportpath, string EventName)
{
try
{
string UrlPath = "/ExcelReports/";
string reportName = "EventSummary_" + EventName + ".xlsx";
string sheetName = "";
UInt32 counter = 0;
File.Copy(templatePath, (exportpath + reportName), true);
using (SpreadsheetDocument document = SpreadsheetDocument.Open((exportpath + reportName), true))
{
wbPart = document.WorkbookPart;
counter = 2;
foreach (RegisteredReportDTO registeredReportDTO in reportDTO.RegisteredReportDTOs)
{
sheetName = ConstantValues.Participated;
UpdateValue(sheetName, "A", counter, registeredReportDTO.EventName, true);
UpdateValue(sheetName, "B", counter, registeredReportDTO.BeneficaryName, true);
UpdateValue(sheetName, "C", counter, registeredReportDTO.EventDate, true);
UpdateValue(sheetName, "D", counter, registeredReportDTO.Location, true);
UpdateValue(sheetName, "E", counter, registeredReportDTO.EmployeeID, false);
UpdateValue(sheetName, "F", counter, registeredReportDTO.FeedbackTextNumber1, false);
UpdateValue(sheetName, "G", counter, registeredReportDTO.FeedbackTextNumber2, true);
UpdateValue(sheetName, "H", counter, registeredReportDTO.FeedbackTextNumber3, true);
counter++;
}
counter = 2;
foreach (NotAttendedReportDTO notAttendedReportDTO in reportDTO.NotAttendedReportDTOs)
{
sheetName = ConstantValues.NotParticipated;
UpdateValue(sheetName, "A", counter, notAttendedReportDTO.EventName, true);
UpdateValue(sheetName, "B", counter, notAttendedReportDTO.BeneficaryName, true);
UpdateValue(sheetName, "C", counter, notAttendedReportDTO.EventDate, true);
UpdateValue(sheetName, "D", counter, notAttendedReportDTO.Location, true);
UpdateValue(sheetName, "E", counter, notAttendedReportDTO.EmployeeID, false);
UpdateValue(sheetName, "F", counter, notAttendedReportDTO.FeedbackText, true);
counter++;
}
counter = 2;
foreach (UnregisteredReportDTO unregisteredReportDTO in reportDTO.UnregisteredReportDTOs)
{
sheetName = ConstantValues.UnRegistered;
UpdateValue(sheetName, "A", counter, unregisteredReportDTO.EventName, true);
UpdateValue(sheetName, "B", counter, unregisteredReportDTO.BeneficaryName, true);
UpdateValue(sheetName, "C", counter, unregisteredReportDTO.EventDate, true);
UpdateValue(sheetName, "D", counter, unregisteredReportDTO.Location, true);
UpdateValue(sheetName, "E", counter, unregisteredReportDTO.EmployeeID, false);
UpdateValue(sheetName, "F", counter, unregisteredReportDTO.FeedbackText, true);
counter++;
}
document.Close();
}
UrlPath = (UrlPath + reportName);
return(UrlPath);
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "CreateExcel",
ActionrName = "WriteToExcel",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public static void CreateSpreadsheetWorkbook()
{
string filepath = $@"{Constants.Locations.DesktopPath}\{Constants.FTP.ExcelFile}";
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook))
{
// 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 <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadSheet.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet1"
};
sheets.Append(sheet);
// Add minimal Stylesheet
var stylesPart = spreadSheet.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet
{
Fonts = new Fonts(new Font()),
Fills = new Fills(new Fill()),
Borders = new Borders(new Border()),
CellStyleFormats = new CellStyleFormats(new CellFormat()),
CellFormats =
new CellFormats(
new CellFormat(),
new CellFormat
{
NumberFormatId = 14,
ApplyNumberFormat = true
})
};
workbookpart.Workbook.Save();
Cell cell = InsertCellInWorksheet("A", 2, worksheetPart);
// Set the value of cell A1.
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.CellValue = new CellValue("My Name is Kashish Jhaveri");
// Save the new worksheet.
worksheetPart.Worksheet.Save();
}
}
public string ReadAll()
{
try
{
var sb = new StringBuilder();
var ss = SpreadsheetDocument.Open(_file.FullName, false);
var wbPart = ss.WorkbookPart;
var stringTable = wbPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
foreach (var workSheetPart in wbPart.WorksheetParts)
{
var reader = OpenXmlReader.Create(workSheetPart);
while (reader.Read())
{
if (reader.ElementType == typeof(Worksheet))
{
reader.ReadFirstChild();
}
if (reader.ElementType != typeof(Row))
{
continue;
}
var row = (Row)reader.LoadCurrentElement();
foreach (var cell in row.Elements <Cell>())
{
if (cell.CellReference == null || !cell.CellReference.HasValue || cell.DataType == null)
{
continue;
}
var value = cell.InnerText;
switch (cell.DataType.Value)
{
case CellValues.SharedString:
if (stringTable != null)
{
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
value = value switch
{
"0" => "FALSE",
_ => "TRUE"
};
break;
case CellValues.Error:
continue;
}
sb.Append(value);
sb.Append(" ");
}
}
reader.Close();
}
ss.Close();
return(sb.ToString());
}
catch (Exception e)
{
Console.WriteLine(e);
SentrySdk.CaptureException(e);
return(_file.Extension == "" ? _file.Name : _file.Name.Replace(_file.Extension, ""));
}
}
public static bool InitializeSs(string physicalAppPath, string studyId, SSInsertionData ssInsert, int sensorType, int language)
{
physicalAppPath = Encoder.HtmlEncode(physicalAppPath);
studyId = Encoder.HtmlEncode(studyId);
if (DbUtils.IsStudyIdValid(studyId) != 1)
{
return(false);
}
Nlogger.LogInfo("SsUtils.InitializeSS: " + studyId);
var path = physicalAppPath + "sstemplate\\";
var file = path + "Checks_tmpl.xlsm";
var file2 = path + studyId + ".xlsm";
var iSensorType = sensorType;
var iGGonlyModeOn = 0;
if (iSensorType == 0)
{
iGGonlyModeOn = 1;
}
if (!File.Exists(file2))
{
File.Copy(file, file2);
}
Nlogger.LogInfo("SsUtils.InitializeSS - insert data into ss: " + studyId);
try
{
using (var document = SpreadsheetDocument.Open(file2, true))
{
var wbPart = document.WorkbookPart;
var theSheet =
wbPart.Workbook.Descendants <Sheet>().FirstOrDefault(s => s.Name == "ParameterDefaults");
//WorksheetPart wsPart = (WorksheetPart)wbPart.GetPartById(theSheet.Id);
UpdateValue(wbPart, "D2", studyId, 0, true, "ParameterDefaults");
UpdateValue(wbPart, "E2", studyId, 0, true, "ParameterDefaults");
UpdateValue(wbPart, "D3", ssInsert.BodyWeight, 0, false, "ParameterDefaults");
UpdateValue(wbPart, "E3", ssInsert.BodyWeight, 0, false, "ParameterDefaults");
UpdateValue(wbPart, "D4", ssInsert.InsulinConcentration, 0, false, "ParameterDefaults");
UpdateValue(wbPart, "E4", ssInsert.InsulinConcentration, 0, false, "ParameterDefaults");
UpdateValue(wbPart, "D5", ssInsert.TargetLow, 0, false, "ParameterDefaults");
UpdateValue(wbPart, "E5", ssInsert.TargetLow, 0, false, "ParameterDefaults");
UpdateValue(wbPart, "D6", ssInsert.TargetHigh, 0, false, "ParameterDefaults");
UpdateValue(wbPart, "E6", ssInsert.TargetHigh, 0, false, "ParameterDefaults");
UpdateValue(wbPart, "D47", iGGonlyModeOn.ToString(), 0, false, "ParameterDefaults");
UpdateValue(wbPart, "D49", iSensorType.ToString(), 0, false, "ParameterDefaults");
UpdateValue(wbPart, "D50", "", 0, false, "ParameterDefaults");
UpdateValue(wbPart, "D51", "", 0, false, "ParameterDefaults");
UpdateValue(wbPart, "D53", language.ToString(), 0, false, "ParameterDefaults");
if (sensorType > 0)
{
UpdateValue(wbPart, "A2", "1", 0, true, "SensorData");
UpdateValue(wbPart, "B2", ssInsert.MonitorDate, 0, true, "SensorData");
UpdateValue(wbPart, "C2", ssInsert.MonitorTime, 0, true, "SensorData");
UpdateValue(wbPart, "D2", ssInsert.MonitorId, 0, true, "SensorData");
UpdateValue(wbPart, "E2", ssInsert.TransmitterId, 0, true, "SensorData");
UpdateValue(wbPart, "F2", ssInsert.SensorLot, 0, true, "SensorData");
UpdateValue(wbPart, "G2", ssInsert.InserterFirstName, 0, true, "SensorData");
UpdateValue(wbPart, "H2", ssInsert.InserterLastName, 0, true, "SensorData");
UpdateValue(wbPart, "I2", GetSensorLocationString(ssInsert.SensorLocation), 0, true,
"SensorData");
UpdateValue(wbPart, "J2", "Initial Insertion", 0, true, "SensorData");
UpdateValue(wbPart, "K2", DateTime.Today.ToShortDateString(), 0, true, "SensorData");
UpdateValue(wbPart, "L2", ssInsert.ExpirationDate, 0, true, "SensorData");
}
document.Close();
}
//get the path to the site folder
var siteCode = studyId.Substring(0, 2);
var sitePath = physicalAppPath + "xcel\\" + siteCode;
//if it doesn't exist then create it
if (!Directory.Exists(sitePath))
{
Directory.CreateDirectory(sitePath);
}
var file3 = sitePath + "\\" + studyId + ".xlsm";
if (File.Exists(file3))
{
File.Delete(file3);
}
File.Move(file2, file3);
}
catch (Exception ex)
{
Nlogger.LogError(ex);
return(false);
}
return(true);
}
public static DataTable GetFirstWorkSheetData(SpreadsheetDocument ss, bool includeHeaderRow, int headerRowOffset = 0)
{
return(GetWorkSheetData(ss, null, false, headerRowOffset));
}
public static void AddDataToWorkSheet(SpreadsheetDocument ss, String sheetName, DataTable dt_data, bool includeHeaderRow, bool clearSheet, bool addTotalPriceColumn)
{
WorksheetPart wsp_group = GetWorkSheetPartByName(ss, sheetName);
AddDataToWorkSheet(wsp_group, dt_data, includeHeaderRow, clearSheet, addTotalPriceColumn);
}
// Retrieving
public static DataTable GetWorkSheetData(SpreadsheetDocument ss, String sheetName, bool includeHeaderRow, int headerRowOffset = 0)
{
DataTable dt = new DataTable();
WorksheetPart wsp;
if (sheetName != null)
{
wsp = GetWorkSheetPartByName(ss, sheetName);
}
else
{
wsp = GetFirstWorkSheetPart(ss);
}
if (wsp != null)
{
Worksheet ws = wsp.Worksheet;
SheetData sheet_data = ws.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheet_data.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
String ColumnName = GetCellValue(ss, cell);
if (!dt.Columns.Contains(ColumnName))
{
dt.Columns.Add(ColumnName);
}
}
// Fill data table with Excel data.
// This implementation includes blank cells from Excel datasets, meaning the datatable will not
// skew when encoutering skipped elements in the XML file (blank cells are null, and are not included in the XML).
foreach (Row row in rows)
{
DataRow tempRow = dt.NewRow();
int columnIndex = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex = ColumnNames.IndexOf(GetColumnName(cell.CellReference));
// (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
if (columnIndex < cellColumnIndex)
{
do
{
if (tempRow.ItemArray.Length > columnIndex)
{
tempRow[columnIndex] = String.Empty;
}
else
{
break;
}
columnIndex++;
}while (columnIndex < cellColumnIndex);
}
String cell_value = GetCellValue(ss, cell);
if (columnIndex < tempRow.ItemArray.Length)
{
tempRow[columnIndex] = cell_value;
}
columnIndex++;
}
dt.Rows.Add(tempRow);
}
if (headerRowOffset != 0)
{
for (int row = 1; row < (headerRowOffset + 1); row++)
{
dt.Rows.RemoveAt(0);
}
// Attempt to set new DataTable header names (as the headers would have been set earlier, without regard for the header offset)
if (dt.Rows.Count > 0)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
String ColumnName = dt.Rows[0][j].ToString();
if (String.IsNullOrEmpty(ColumnName)) // give dummny name if necessary
{
ColumnName = j.ToString();
}
dt.Columns[j].ColumnName = ColumnName;
}
}
}
if (!includeHeaderRow)
{
dt.Rows.RemoveAt(0);
}
}
return(dt);
}
//InsertText in Exsting excel file
public static void InsertData(List <string> HeaderData, List <List <string> > DataLines)
{
uint rowIndex = 1;
string filepath = $@"{Constants.Locations.DesktopPath}\{Constants.FTP.ExcelFile}";
HeaderData.Insert(0, "UniqueId");
HeaderData.Add("BookAge");
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filepath, true))
{
//UniqueId(generate a Guid from your application using Guid.NewGuid() in C#)
// Insert a new worksheet.
WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
char colname = 'A';
Cell cell;
foreach (var col in HeaderData)
{
cell = InsertCellInWorksheet(Convert.ToString(colname), rowIndex, worksheetPart);
cell.CellValue = new CellValue(col);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
colname++;
}
foreach (List <string> CsvRow in DataLines)
{
if (rowIndex > 100)
{
break;
}
rowIndex++;
cell = InsertCellInWorksheet("A", rowIndex, worksheetPart);
cell.CellValue = new CellValue(Guid.NewGuid().ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.String);
cell = InsertCellInWorksheet("B", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
cell.CellValue = new CellValue(CsvRow[0]);
cell = InsertCellInWorksheet("C", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.CellValue = new CellValue(CsvRow[1]);
cell = InsertCellInWorksheet("D", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.CellValue = new CellValue(CsvRow[2]);
cell = InsertCellInWorksheet("E", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
cell.CellValue = new CellValue(CsvRow[3]);
cell = InsertCellInWorksheet("F", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
cell.CellValue = new CellValue(CsvRow[4]);
cell = InsertCellInWorksheet("G", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.CellValue = new CellValue(CsvRow[5]);
cell = InsertCellInWorksheet("H", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
cell.CellValue = new CellValue(CsvRow[6]);
cell = InsertCellInWorksheet("I", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
cell.CellValue = new CellValue(CsvRow[7]);
cell = InsertCellInWorksheet("J", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
cell.CellValue = new CellValue(CsvRow[8]);
cell = InsertCellInWorksheet("K", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
cell.CellValue = new CellValue(CsvRow[9]);
cell = InsertCellInWorksheet("L", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.Date);
cell.CellValue = new CellValue(Convert.ToDateTime(CsvRow[10]).ToOADate().ToString(CultureInfo.InvariantCulture));
cell = InsertCellInWorksheet("M", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.CellValue = new CellValue(CsvRow[11]);
//Calculated Age of book
cell = InsertCellInWorksheet("N", rowIndex, worksheetPart);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
cell.CellFormula = new CellFormula($"=INT((TODAY()-L{rowIndex})/365)");
}
// Save the new worksheet.
worksheetPart.Worksheet.Save();
spreadSheet.Close();
}
}
/// <summary>Saves one worksheet in the spreadsheet. You can add one or more.</summary>
/// <typeparam name="T">The class type that contains data that will be added to the worksheet</typeparam>
/// <param name="dataList">The data that will be added to the worksheet</param>
/// <param name="sheetName">The name of the worksheet</param>
/// <param name="createHeaderRow">Indicates if the worksheet has a header row.</param>
/// <returns>A worksheet</returns>
public void AddWorksheet <T>(List <T> dataList, string sheetName, bool createHeaderRow = true) where T : class
{
if (dataList == null)
{
throw new ArgumentException("You must specify a list of data!");
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = string.Format("sheet{0}", _spreadSheetId);
}
if (_spreadSheet == null)
{
_ms = new MemoryStream();
_spreadSheet = SpreadsheetDocument.Create(_ms, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
_workbookpart = _spreadSheet.AddWorkbookPart();
_workbookpart.Workbook = new Workbook();
SharedStringTablePart shareStringPart = _workbookpart.AddNewPart <SharedStringTablePart>();
shareStringPart.SharedStringTable = new SharedStringTable();
// Add Sheets to the Workbook.
_sheets = _spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
// Add minimal Stylesheet
// http://stackoverflow.com/questions/2792304/how-to-insert-a-date-to-an-open-xml-worksheet
var stylesPart = _workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet
{
Fonts = new Fonts(new Font()),
Fills = new Fills(new Fill()),
Borders = new Borders(new Border()),
CellStyleFormats = new CellStyleFormats(new CellFormat()),
CellFormats = new CellFormats(
// empty one for index 0, seems to be required
new CellFormat()),
NumberingFormats = new NumberingFormats()
};
}
List <ClassToExcelColumn> columns = CreateColumns(sheetName, typeof(T));
CreateAdditionalCellFormats(columns);
// For each Excel sheet (that has separate data)
// - a separate WorkSheetPart object is needed
// - a separate WorkSheet object is needed
// - a separate SheetData object is needed
// - a separate Sheet object is needed
// Ref: http://stackoverflow.com/a/22230453/97803
// Also see: https://msdn.microsoft.com/en-us/library/office/cc881781.aspx
// Add a WorkSheetPart
WorksheetPart worksheetPart = _workbookpart.AddNewPart <WorksheetPart>();
// Append Sheet
Sheet sheet = new Sheet
{
Id = _spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = _spreadSheetId++,
Name = sheetName
};
_sheets.Append(sheet);
// Add a Worksheet
Worksheet worksheet = new Worksheet();
worksheetPart.Worksheet = worksheet;
// Create and add SheetData
SheetData sheetData = CreateSheetData(dataList, createHeaderRow, columns);
worksheet.AppendChild(sheetData);
worksheet.Save();
}
public DocumentContext(SpreadsheetDocument document, IExcelMetadata documentMetadata)
{
Document = document;
DocumentMetadata = documentMetadata.AssertArgumentNotNull(nameof(documentMetadata));
}
// Returns the row and column numbers and worksheet part for the named range
public static WorksheetPart GetFormula(SpreadsheetDocument doc, string formula, out string sheetName, out int startRow, out int startColumn, out int endRow, out int endColumn)
{
sheetName = GetFormulaCoord(formula, out startRow, out startColumn, out endRow, out endColumn);
return(WorksheetAccessor.GetWorksheet(doc, sheetName));
}
public static object ExportToExcel(string model, string data, string title)
{
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
/* Create the worksheet. */
SpreadsheetDocument spreadsheet = Excel.CreateWorkbook(stream);
Excel.AddBasicStyles(spreadsheet);
Excel.AddAdditionalStyles(spreadsheet);
Excel.AddWorksheet(spreadsheet, title);
Worksheet worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
/* Get the information needed for the worksheet */
var modelObject = JsonConvert.DeserializeObject <dynamic>(model);
var dataObject = JsonConvert.DeserializeObject <dynamic>(data);
/* Add the column titles to the worksheet. */
// For each column...
for (int mdx = 0; mdx < modelObject.Count; mdx++)
{
// If the column has a title, use it. Otherwise, use the field name.
Excel.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(mdx + 1),
(modelObject[mdx].title == null || modelObject[mdx].title == " ")
? modelObject[mdx].field.ToString()
: modelObject[mdx].title.ToString(),
false, false);
// Is there are column width defined?
Excel.SetColumnWidth(worksheet, mdx + 1, modelObject[mdx].width != null
? int.Parse(LeadingInteger.Match(modelObject[mdx].width.ToString()).Value) / 4
: 25);
}
/* Add the data to the worksheet. */
// For each row of data...
for (int idx = 0; idx < dataObject.Count; idx++)
{
// For each column...
for (int mdx = 0; mdx < modelObject.Count; mdx++)
{
// Set the field value in the spreadsheet for the current row and column.
Excel.SetCellValue(spreadsheet, worksheet, Convert.ToUInt32(mdx + 1), Convert.ToUInt32(idx + 2),
dataObject[idx][modelObject[mdx].field.ToString()].ToString(),
false, false);
}
}
/* Save the worksheet and store it in Session using the spreadsheet title. */
worksheet.Save();
spreadsheet.Close();
byte[] file = stream.ToArray();
HttpContext.Current.Session[title] = file;
}
return(new { success = true });
}
public static ExcelEntity ReadExcelDetail(SpreadsheetDocument excel, List <string> sheetName, string file)
{
ExcelEntity ee = new ExcelEntity();
#region SheetStyle公用样式表
#region 主题色
ThemePart themPart = excel.WorkbookPart.ThemePart;
var themColor = ThemeColor.GetThemeColorList(themPart);
#endregion
//获取样式设置
Stylesheet styleSheet = excel.WorkbookPart.WorkbookStylesPart.Stylesheet;
#region 样式列表
CellFormats cellFormats = styleSheet.CellFormats;
List <CellFormatsList> cellFormatsList = new List <CellFormatsList>();
int index = 0;
foreach (CellFormat cell in cellFormats.ChildElements)
{
if (cell != null)
{
CellFormatsList cfl = new CellFormatsList();
cfl.styleIndex = index;
if (cell.NumberFormatId != null)
{
cfl.numFmtId = int.Parse(cell.NumberFormatId);
}
if (cell.FontId != null)
{
cfl.fontId = int.Parse(cell.FontId);
}
if (cell.FillId != null)
{
cfl.fillId = int.Parse(cell.FillId);
}
if (cell.BorderId != null)
{
cfl.borderId = int.Parse(cell.BorderId);
}
if (cell.ApplyAlignment != null)
{
cfl.applyAlignment = int.Parse(cell.ApplyAlignment);
}
if (cell.ApplyBorder != null)
{
cfl.applyBorder = int.Parse(cell.ApplyBorder);
}
if (cell.ApplyFont != null)
{
cfl.applyFont = int.Parse(cell.ApplyFont);
}
if (cell.ApplyNumberFormat != null)
{
cfl.applyNumberFormat = int.Parse(cell.ApplyNumberFormat);
}
if (cell.Alignment != null)
{
string ver = cell.Alignment.Vertical;
string hor = cell.Alignment.Horizontal;
if (!string.IsNullOrEmpty(ver))
{
if (ver == "center")
{
cfl.vertical = "htMiddle";
}
else
{
cfl.vertical = "ht" + ver.Substring(0, 1).ToUpper() + ver.Substring(1, ver.Length - 1);
}
}
else
{
cfl.vertical = "htBottom";
}
if (!string.IsNullOrEmpty(hor))
{
cfl.horizontal = "ht" + hor.Substring(0, 1).ToUpper() + hor.Substring(1, hor.Length - 1);
}
else
{
cfl.horizontal = "htLeft";
}
}
cellFormatsList.Add(cfl);
index++;
}
}
ee.CellFormatsList = cellFormatsList;
#endregion
#region 数据类型列表
NumberingFormats numberFormats = styleSheet.NumberingFormats;
List <NumFmtsList> numFmtList = new List <NumFmtsList>();
if (numberFormats != null)
{
foreach (NumberingFormat cell in numberFormats.ChildElements)
{
NumFmtsList nfl = new NumFmtsList();
if (cell.NumberFormatId != null)
{
nfl.numFmtId = (int)cell.NumberFormatId.Value;
}
if (cell.FormatCode != null)
{
nfl.formatCode = cell.FormatCode.Value;
}
numFmtList.Add(nfl);
}
}
ee.NumFmtsList = numFmtList;
#endregion
#region 字体样式
Fonts fonts = styleSheet.Fonts;
List <FontsList> fontsList = new List <FontsList>();
foreach (Font cell in fonts.ChildElements)
{
FontsList fl = new FontsList();
if (cell.FontSize != null)
{
fl.fontsize = cell.FontSize.Val + "px";
}
if (cell.FontName != null)
{
fl.fontname = cell.FontName.Val;
}
if (cell.Color != null)
{
if (cell.Color.Rgb != null && !string.IsNullOrEmpty(cell.Color.Rgb.ToString()))
{
fl.color = "#" + cell.Color.Rgb.ToString().Substring(2, 6);
}
}
if (cell.Italic != null)
{
fl.italic = "italic";
}
if (cell.Bold != null)
{
fl.bold = "bold";
}
if (cell.Underline != null)
{
fl.underline = "underline";
}
fontsList.Add(fl);
}
ee.FontsList = fontsList;
#endregion
#region 填充色样式
Fills fills = styleSheet.Fills;
List <FillsList> fillsList = new List <FillsList>();
foreach (Fill cell in fills.ChildElements)
{
FillsList fl = new FillsList();
if (cell.PatternFill != null)
{
fl.patternType = cell.PatternFill.PatternType;
if (cell.PatternFill.ForegroundColor != null)
{
if (cell.PatternFill.ForegroundColor.Rgb != null)
{
fl.fgColor = "#" + cell.PatternFill.ForegroundColor.Rgb.ToString().Substring(2, 6);
}
if (cell.PatternFill.ForegroundColor.Theme != null)
{
UInt32Value themeIndex = cell.PatternFill.ForegroundColor.Theme;
DoubleValue tint = cell.PatternFill.ForegroundColor.Tint;
if (tint != null)
{
var newColor = ThemeColor.ThemColorDeal(themeIndex, tint, themColor[themeIndex]);
fl.fgColor = "#" + newColor.Name.Substring(2, 6);
fl.fgColor = "#" + newColor.Name.Substring(2, 6);
}
else
{
fl.fgColor = "#" + themColor[themeIndex];
fl.fgColor = "#" + themColor[themeIndex];
}
}
}
}
fillsList.Add(fl);
}
ee.FillsList = fillsList;
#endregion
#region 边框样式
Borders borders = styleSheet.Borders;
List <BordersList> bordersList = new List <BordersList>();
var defaultBorderStyle = "1px solid #000";
foreach (Border cell in borders.ChildElements)
{
BordersList bl = new BordersList();
if (cell.LeftBorder != null)
{
if (cell.LeftBorder.Style != null)
{
bl.left = defaultBorderStyle;
}
}
if (cell.RightBorder != null)
{
if (cell.RightBorder.Style != null)
{
bl.right = defaultBorderStyle;
}
}
if (cell.TopBorder != null)
{
if (cell.TopBorder.Style != null)
{
bl.top = defaultBorderStyle;
}
}
if (cell.BottomBorder != null)
{
if (cell.BottomBorder.Style != null)
{
bl.bottom = defaultBorderStyle;
}
}
if (cell.DiagonalBorder != null)
{
if (cell.DiagonalBorder.Style != null)
{
bl.diagonal = cell.DiagonalBorder.Style;
}
}
bordersList.Add(bl);
}
ee.BordersList = bordersList;
#endregion
#endregion
List <SheetDataList> sheetDataList = new List <SheetDataList>();
List <PictureInfo> pictures = null;
for (int i = 0; i < sheetName.Count; i++)
{
SheetDataList sdl = new SheetDataList();
int RowCount = 0;
int ColumnCount = 0;
//得到工作表dimension
WorksheetPart worksheet = ExcelHelper.GetWorksheetPartByName(excel, sheetName[i]);
#region 获取单个Sheet表的数据
#region //批注
WorksheetCommentsPart comments = worksheet.WorksheetCommentsPart;
List <CommentCellsList> commentLists = new List <CommentCellsList>();
if (comments != null)
{
CommentList commentList = (CommentList)comments.Comments.ChildElements[1];
//批注列表
foreach (Comment comment in commentList.ChildElements)
{
CommentCellsList ccl = new CommentCellsList();
//坐标
var cell = GetCellXY(comment.Reference).Split('_');
var columnRow = int.Parse(cell[0].ToString()) - 1;
var columnCol = GetColumnIndex(cell[1]);
//批注内容
var commentVal = comment.InnerText;
ccl.row = columnRow;
ccl.col = columnCol;
ccl.comment = comment.InnerText;
//var commentCell = "{\"Row\":\""+ columnRow + "\",\"Col\":\"" + columnCol + ",\"Comment\":\"" + commentVal + "\"}";
commentLists.Add(ccl);
}
}
sdl.Comments = commentLists;
#endregion
#region //获取合并单元格
IEnumerable <MergeCells> mergeCells = worksheet.Worksheet.Elements <MergeCells>();
List <MergeCellsList> mergeCellList = new List <MergeCellsList>();
if (mergeCells.Count() > 0)
{
for (int k = 0; k < mergeCells.First().ChildElements.Count; k++)
{
MergeCell mergeCell = (MergeCell)mergeCells.First().ChildElements[k];
var reference = mergeCell.Reference.ToString().Split(':');
var startCell = GetCellXY(reference[0]).Split('_');
var endCell = GetCellXY(reference[1]).Split('_');
MergeCellsList mcl = new MergeCellsList();
mcl.row = int.Parse(startCell[0]) - 1;
mcl.rowspan = int.Parse(endCell[0]) - int.Parse(startCell[0]) + 1;
mcl.col = GetColumnIndex(startCell[1]);
mcl.colspan = GetColumnIndex(endCell[1]) - mcl.col + 1;
//mcl.reference = mergeCell.Reference.ToString();
mergeCellList.Add(mcl);
}
}
sdl.MergeCells = mergeCellList;
#endregion
//获取超链接列表
//var hyperlinks = worksheet.RootElement.Descendants<Hyperlinks>().First().Cast<Hyperlink>();
#region //读取图片
DrawingsPart drawingPart = worksheet.GetPartsOfType <DrawingsPart>().ToList().FirstOrDefault();
pictures = new List <PictureInfo>();
if (drawingPart != null)
{
int tempIndex = 1;
foreach (var part in drawingPart.Parts)
{
PictureInfo pic = new PictureInfo();
ImagePart imgPart = (ImagePart)part.OpenXmlPart;
System.Drawing.Image img1 = System.Drawing.Image.FromStream(imgPart.GetStream());
var newFilename = Guid.NewGuid().ToString("N") + ".png";
string[] sArray = Regex.Split(file, "UserFile", RegexOptions.IgnoreCase);
string newFilePath = sArray[0] + "_Temp\\" + newFilename;
img1.Save(newFilePath);
//pic.Image = img1;
pic.RefId = part.RelationshipId;//"rId" + imgPart.Uri.ToString().Split('/')[3].Split('.')[0].Substring(5);
pic.ImageUrl = newFilePath;
pic.ImageName = newFilename;
pic.ImgHeight = img1.Height;
pic.ImgWidth = img1.Width;
pictures.Add(pic);
tempIndex++;
}
//获取图片定位
var worksheetDrawings = drawingPart.WorksheetDrawing.Where(c => c.ChildElements.Any
(a => a.GetType().FullName == "DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture")).ToList();
foreach (var worksheetDrawing in worksheetDrawings)
{
if (worksheetDrawing.GetType().FullName ==
"DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor")
{
TwoCellAnchor anchor = (TwoCellAnchor)worksheetDrawing;
DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picDef =
(DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture)
anchor.ChildElements.FirstOrDefault(c => c.GetType().FullName ==
"DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture");
if (picDef != null)
{
var embed = picDef.BlipFill.Blip.Embed;
if (embed != null)
{
var picMapping = pictures.FirstOrDefault(c => c.RefId == embed.InnerText);
picMapping.FromCol = int.Parse(anchor.FromMarker.ColumnId.InnerText);
picMapping.FromRow = int.Parse(anchor.FromMarker.RowId.InnerText);
}
}
// anchor.FromMarker.RowId + anchor.FromMarker.ColumnId
}
}
}
sdl.PictureList = pictures;
#endregion
#region 读取表格数据
List <SheetDatas> sheetDatas = new List <SheetDatas>();
if (worksheet.Rows().Count() > 0)
{
RowCount = int.Parse((worksheet.Rows().Last()).RowId);
}
int TempColumn = 0;
foreach (OpenXmlPowerTools.Row row in worksheet.Rows())
{
foreach (OpenXmlPowerTools.Cell cell in row.Cells())
{
#region 读取超链接
//var hyperlink = hyperlinks.SingleOrDefault(c => c.Reference.Value == cell.Column);
//if (hyperlink != null)
//{
// var hyperlinksRelation = worksheet.HyperlinkRelationships.SingleOrDefault(c => c.Id == hyperlink.Id);
// if (hyperlinksRelation != null)
// {
// //这是最终我们需要的超链接
// var url = hyperlinksRelation.Uri.ToString();
// }
//}
#endregion
TempColumn = (row.Cells().Last()).ColumnIndex;
if (ColumnCount < TempColumn)
{
ColumnCount = TempColumn + 1;
}
SheetDatas sheetData = new SheetDatas();
sheetData.RowId = int.Parse(row.RowId);
sheetData.ColumnId = cell.ColumnIndex;
sheetData.Column = cell.Column;
sheetData.Type = cell.Type;
sheetData.Value = cell.Value;
sheetData.SharedString = cell.SharedString;
sheetData.Formula = cell.Formula;
sheetData.StyleId = cell.Style;
#region 样式赋值
if (sheetData.StyleId != null)
{
CellFormatsList cfl = cellFormatsList[(int)sheetData.StyleId];
sheetData.FontName = fontsList[cfl.fontId].fontname;
sheetData.FontSize = fontsList[cfl.fontId].fontsize;
sheetData.FontColor = fontsList[cfl.fontId].color;
sheetData.FontBold = fontsList[cfl.fontId].bold;
sheetData.Italic = fontsList[cfl.fontId].italic;
sheetData.Underline = fontsList[cfl.fontId].underline;
sheetData.AligmentVertical = cfl.vertical;
sheetData.AligmentHorizontal = cfl.horizontal;
sheetData.FillType = fillsList[cfl.fillId].patternType;
sheetData.FillForegroundColor = fillsList[cfl.fillId].fgColor;
sheetData.FillBackgroundColor = fillsList[cfl.fillId].bgColor;
sheetData.LeftBorder = bordersList[cfl.borderId].left;
sheetData.RightBorder = bordersList[cfl.borderId].right;
sheetData.TopBorder = bordersList[cfl.borderId].top;
sheetData.BottomBorder = bordersList[cfl.borderId].bottom;
sheetData.DiagonalBorder = bordersList[cfl.borderId].diagonal;
}
#endregion
if (cell.Style != null)
{
var cellf = cellFormatsList[(int)cell.Style];
if (cellf.applyNumberFormat > 0 && cell.Type == null && cell.Value != null)
{
//for (int n = 0; n < numFmtList.Count; n++)
//{
// if (numFmtList[n].numFmtId == cellf.numFmtId|| cellf.numFmtId == 14)
// {
// sheetData.Type = "s";
// sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToShortDateString();
// break;
// }
//}
if (cellf.numFmtId == 58)
{
sheetData.Type = "s";
sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToString("M月d日");
}
else if (cellf.numFmtId == 14)
{
sheetData.Type = "s";
sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToShortDateString();
}
else
{
for (int n = 0; n < numFmtList.Count; n++)
{
if (numFmtList[n].numFmtId == cellf.numFmtId)
{
sheetData.Type = "s";
sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToShortDateString();
break;
}
}
}
}
else
{
sheetData.Value = cell.Value;
}
}
sheetDatas.Add(sheetData);
}
}
if (ColumnCount < 5)
{
ColumnCount = 5;
}
if (RowCount < 20)
{
RowCount = 20;
}
sdl.SheetData = sheetDatas;
#endregion
sdl.SheetName = sheetName[i];
sdl.SheetId = "sheet" + (i + 1);
sdl.TotalRow = (RowCount + 1);
sdl.TotalColumn = ColumnCount;
sheetDataList.Add(sdl);
//htmlStr = EMW.API.Serializer.ObjectToString(sheetDataList);//
//htmlStr = "{\"SheetData\":" + htmlStr + ",\"Comments\":" + EMW.API.Serializer.ObjectToString(commentLists) + ",\"MergeCells\":" + EMW.API.Serializer.ObjectToString(mergeCellList) + ",\"TotalRow\":" + (RowCount + 1) + ",\"TotalColumn\":" + ColumnCount + ",\"SheetName\":\"" + sheetName[i] + "\"}";
//htmlSheet.Add(htmlStr);
#endregion
}
ee.SheetDataList = sheetDataList;
return(ee);
}
/// <summary>
/// Below function has been created to proecess request as per request id.
/// </summary>
/// <param name="id">process request queue id</param>
/// <returns></returns>
public static async Task <bool> ExecuteExportRequest(int id)
{
string sqlConstr = Environment.GetEnvironmentVariable("sqldb_connection");
int Prq_Header_Id = 0;
int Prq_Id = 0;
try
{
// Step 1: Check is there any request exists with Pending status in PROCESS_REQUEST_QUEUE table for Specified PRQ_ID and PRQ_Header_ID.
// Step 2: If not Pending Request Exists then Exit task.
// Step 3: From Parameter get Program and Universe and from , to Date.
// Step 4: Update PRQ Status to InProgress and Call SP as per Report type.
// Step 5: Call SP based on Parameteres and return dataset.
// Step 6: Define File Name and create file in memory.
// Step 7: Write DataSet (Step 5) data to File memeory.
// Step 8: Using File Memory object Upload file to Azure Blob Storage.
// Step 9: Update PRQ table Status, FileName,FileLink , Updateby Columns.
// Step 10:Add Notification to User.
// Step 11:Send email using SendGrid to user to Download Request.
// Step 1: Check is there any request exists with Pending status in PROCESS_REQUEST_QUEUE table for Specified PRQ_ID and PRQ_Header_ID.
using (SqlConnection connObj = new SqlConnection(sqlConstr))
{
connObj.AccessToken = await GetDatabaseToken();
SqlDataAdapter myCommand = new SqlDataAdapter("usp_Get_Process_Request_Queue", connObj);
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@PRQ_HEADER_ID", SqlDbType.Int));
myCommand.SelectCommand.Parameters["@PRQ_HEADER_ID"].Value = id;
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
myCommand.Fill(ds);
// Step 2: If not Pending Request Exists for requested PRQID then Exit task.
if (ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
string UserName = string.Empty;
string UsermailID = string.Empty;
int UserID = 0;
string NotificationFlag = string.Empty;
foreach (DataRow row in dt.Rows)
{
// Step 3: From Parameter get Program,start date,end date,reprot type,universe etc.
Prq_Id = int.Parse(row[0].ToString());
Prq_Header_Id = int.Parse(row[1].ToString());
string strPrqParameters = row[5].ToString();
string FtrType = row[7].ToString();
UserName = row[10].ToString();
UserID = int.Parse(row[11].ToString());
NotificationFlag = row[13].ToString();
UsermailID = row[14].ToString();
// Parameter Format (Comma Seperated) : {programName,fromdate,todate,reportType,FileHeaderFlag,universe}
string[] exportParameters = strPrqParameters.Split(',');
string ProgramName = exportParameters[0].ToString();
string FromDateVal = exportParameters[1].ToString().Replace("12:00:00", " ");
string ToDateVal = exportParameters[2].ToString().Replace("12:00:00", " ");
string ReportType = exportParameters[3].ToString();
string FileHeaderFlag = exportParameters[4].ToString();
string UniverseName = exportParameters[5].ToString();
// Step 4: Update PRQ Status to InProgress and Call SP as per Report type.
myCommand.Dispose();
bool retval = UpdatePRQStatus(connObj, Prq_Id, Prq_Header_Id, "INPROGRESS", "", "", "", "");
// Step 5: Call SP based on Parameteres and return dataset.
DataSet retvalDataSet = GetProcessRequestData(connObj, exportParameters);
if (retvalDataSet.Tables[0].Columns[0].ToString() == "ErrorNumber")
{
throw new Exception(retvalDataSet.Tables[0].Rows[0][5].ToString());
}
// Step 6: Define File Name and create file in memory. (ProgramName+"_"+UniverseName+"_"UserName�+�"_"�+�DateTime.Now.ToString("yyyyMMddHHmmss")�+�FtrType )
string fileName = ProgramName + "_" + UniverseName + "_" + ReportType + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + FtrType;
// Step 7: Write DataSet (Step 5) data to File memeory.
MemoryStream ms = new MemoryStream();
switch (FtrType)
{
case ".csv":
ms = WriteCsvFile(retvalDataSet.Tables[0], ReportType, FromDateVal, ToDateVal, FileHeaderFlag);
break;
case ".xlsx":
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WriteExcelFile(retvalDataSet, document, ReportType, FromDateVal, ToDateVal, FileHeaderFlag);
}
break;
case ".txt":
WritetxtFile(retvalDataSet.Tables[0], ms, ReportType, FromDateVal, ToDateVal, FileHeaderFlag);
break;
}
//You need to create a storage account and put your azure storage connection string in following place
// Step 8: Using File Memory object Upload file to Azure Blob Storage.
string Constr = Environment.GetEnvironmentVariable("StorageConnection");
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(Constr);
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
CloudBlobContainer container = blobClient.GetContainerReference("processrequestcontainer");
container.CreateIfNotExists();
CloudBlockBlob blockBlob = container.GetBlockBlobReference(fileName);
ms.Position = 0;
blockBlob.UploadFromStream(ms);
ms.Flush();
string storageUri = Environment.GetEnvironmentVariable("storageUri");
string strAccessToken = Environment.GetEnvironmentVariable("AccessToken");
string FileLink = storageUri + fileName + strAccessToken;
// Step 9: Update PRQ table Status, FileName,FileLink , Updateby Columns.
bool retvalupdate = UpdatePRQStatus(connObj, Prq_Id, Prq_Header_Id, "DONE", fileName, FileLink, "", "");
}
// Step 10:Add Notification to User if notification is required by Proecess Request.
if (NotificationFlag == "Y")
{
bool retvalNotification = AddNotification(connObj, UserID, Prq_Header_Id, UserName);
// Call Mail Notification SP here and get EQID back.
if (retvalNotification == true)
{
int EQ_ID = AddEmailNotification(connObj, Environment.GetEnvironmentVariable("mailfromgroupid"), UsermailID, 2, UserName, id);
// Web Hook to mail notification service.
HttpClient client = new HttpClient();
var url = Environment.GetEnvironmentVariable("mailnotificationurl") + EQ_ID + Environment.GetEnvironmentVariable("mailnotificationazurecode");
var response = await client.GetAsync(url);
string result = await response.Content.ReadAsStringAsync();
}
}
}
else
{
//TODO : Make SQL DB Log.
// NO Valid Record exist for requested PRQ ID.
throw new Exception("Invalid Request ID.");
//return false;
}
}
return(true);
}
catch (Exception ex)
{
using (SqlConnection connObjError = new SqlConnection(sqlConstr))
{
connObjError.AccessToken = await GetDatabaseToken();
bool retval = UpdatePRQStatus(connObjError, Prq_Id, Prq_Header_Id, "ERROR", "", "", "0001", ex.Message + " Request ID : " + Prq_Header_Id);
}
throw ex;
}
}
private void InsertConfussionMatrixDataIntoSheet(SpreadsheetDocument spreadsheetDocument, string sheetName, ConfusionMatrix confusionMatrix)
{
//Fix for https://github.com/OfficeDev/Open-XML-SDK/issues/221
Environment.SetEnvironmentVariable("MONO_URI_DOTNETRELATIVEORABSOLUTE", "true");
// Open the document for editing
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//Set the sheet name on the second sheet
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
Sheet sheet = sheets.Elements <Sheet>().ElementAtOrDefault(1);
sheet.Name = sheetName;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAtOrDefault(1);
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
// CC
Row dataRow = sheetData.AppendChild(new Row());
Cell cell = ConstructCell("CC", CellValues.String);
dataRow.Append(cell);
cell = ConstructCell(confusionMatrix.CorrectCommission.ToString(), CellValues.Number);
dataRow.Append(cell);
// WC
dataRow = sheetData.AppendChild(new Row());
cell = ConstructCell("WC", CellValues.String);
dataRow.Append(cell);
cell = ConstructCell(confusionMatrix.WrongCommission.ToString(), CellValues.Number);
dataRow.Append(cell);
// WO
dataRow = sheetData.AppendChild(new Row());
cell = ConstructCell("WO", CellValues.String);
dataRow.Append(cell);
cell = ConstructCell(confusionMatrix.WrongOmission.ToString(), CellValues.Number);
dataRow.Append(cell);
// CO
dataRow = sheetData.AppendChild(new Row());
cell = ConstructCell("CO", CellValues.String);
dataRow.Append(cell);
cell = ConstructCell(confusionMatrix.CorrectOmission.ToString(), CellValues.Number);
dataRow.Append(cell);
// Total presses
dataRow = sheetData.AppendChild(new Row());
cell = ConstructCell("Total presses", CellValues.String);
dataRow.Append(cell);
cell = ConstructCell(confusionMatrix.TotalPresses.ToString(), CellValues.Number);
dataRow.Append(cell);
// Total omissions
dataRow = sheetData.AppendChild(new Row());
cell = ConstructCell("Total omissions", CellValues.String);
dataRow.Append(cell);
cell = ConstructCell(confusionMatrix.TotalOmissions.ToString(), CellValues.Number);
dataRow.Append(cell);
workbookPart.Workbook.Save();
}
private void InsertDataIntoSheet(SpreadsheetDocument spreadsheetDocument, string sheetName, Game game)
{
//Fix for https://github.com/OfficeDev/Open-XML-SDK/issues/221
Environment.SetEnvironmentVariable("MONO_URI_DOTNETRELATIVEORABSOLUTE", "true");
// Open the document for editing
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//Set the sheet name on the first sheet
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
Sheet sheet = sheets.Elements <Sheet>().FirstOrDefault();
sheet.Name = sheetName;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
// Header
Row headerRow = sheetData.AppendChild(new Row());
string[] headers = { "Slide number",
"Slide",
"Response",
"Slide displayed",
"Slide hidden" };
foreach (string header in headers)
{
Cell cell = ConstructCell(header, CellValues.String);
headerRow.Append(cell);
}
// Slides
int slideIndex = 0;
int maxResponseCount = 0;
foreach (Slide slide in game.Slides)
{
Row dataRow = sheetData.AppendChild(new Row());
Cell cell = ConstructCell((slideIndex + 1).ToString(), CellValues.String);
dataRow.Append(cell);
cell = ConstructCell(slide.SlideType.ToString(), CellValues.String);
dataRow.Append(cell);
cell = ConstructCell(slide.ResponseOutcome.ToString(), CellValues.String);
dataRow.Append(cell);
cell = ConstructCell(slide.SlideDisplayed.ToString("dd/MM/yyyy HH:mm:ss.fff"), CellValues.String);
dataRow.Append(cell);
cell = ConstructCell(slide.SlideHidden.Value.ToString("dd/MM/yyyy HH:mm:ss.fff"), CellValues.String);
dataRow.Append(cell);
// Responses
int responseIndex = 1;
var responses = game.SensoryData.ButtonPresses.Where(x => x.SlideIndex == slideIndex);
maxResponseCount = (responses.Count() > maxResponseCount) ? responses.Count() : maxResponseCount;
foreach (ButtonPress buttonPress in responses)
{
// Add data
cell = ConstructCell(buttonPress.ToString(), CellValues.String);
dataRow.Append(cell);
responseIndex++;
}
slideIndex++;
}
// Add dynamic headers for response
for (int i = 1; i <= maxResponseCount; i++)
{
// Add header
Cell cell = ConstructCell($"Press {i} time and coord", CellValues.String);
headerRow.Append(cell);
}
workbookPart.Workbook.Save();
}
public FileStreamResult ToExcel(IQueryable query, string fileName = null)
{
var columns = GetProperties(query.ElementType);
var stream = new MemoryStream();
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
GenerateWorkbookStylesPartContent(workbookStylesPart);
var sheets = workbookPart.Workbook.AppendChild(new Sheets());
var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
sheets.Append(sheet);
workbookPart.Workbook.Save();
var sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
var headerRow = new Row();
foreach (var column in columns)
{
headerRow.Append(new Cell()
{
CellValue = new CellValue(column.Key),
DataType = new EnumValue<CellValues>(CellValues.String)
});
}
sheetData.AppendChild(headerRow);
foreach (var item in query)
{
var row = new Row();
foreach (var column in columns)
{
var value = GetValue(item, column.Key);
var stringValue = $"{value}".Trim();
var cell = new Cell();
var underlyingType = column.Value.IsGenericType &&
column.Value.GetGenericTypeDefinition() == typeof(Nullable<>) ?
Nullable.GetUnderlyingType(column.Value) : column.Value;
var typeCode = Type.GetTypeCode(underlyingType);
if (typeCode == TypeCode.DateTime)
{
if (!string.IsNullOrWhiteSpace(stringValue))
{
cell.CellValue = new CellValue() { Text = ((DateTime)value).ToOADate().ToString(System.Globalization.CultureInfo.InvariantCulture) };
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
cell.StyleIndex = (UInt32Value)1U;
}
}
else if (typeCode == TypeCode.Boolean)
{
cell.CellValue = new CellValue(stringValue.ToLower());
cell.DataType = new EnumValue<CellValues>(CellValues.Boolean);
}
else if (IsNumeric(typeCode))
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
else
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
}
row.Append(cell);
}
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
}
if (stream?.Length > 0)
{
stream.Seek(0, SeekOrigin.Begin);
}
var result = new FileStreamResult(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
result.FileDownloadName = (!string.IsNullOrEmpty(fileName) ? fileName : "Export") + ".xlsx";
return result;
}
public override byte[] Render(global::Campus.Report.Base.Report report)
{
byte[] buffer;
using (var outputMemoryStream = new MemoryStream())
{
using (var document = SpreadsheetDocument.Create(outputMemoryStream, SpreadsheetDocumentType.Workbook, true))
{
// Add a WorkbookPart to the document.
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
var sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet1"
};
sheets.Append(sheet);
// Add a WorkbookStylesPart to the WorkbookPart
var stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
var shData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
stylesPart.Stylesheet = InitializeDefaultStyles(stylesPart.Stylesheet);
uint styleId = 2; //обязательно 2 потому что уже додано два стиля по умолчанию с индексами 0 и 1;
var styles = report.Select(o => o.Style).ToList();
//нельзя перенести в отдельный метод :(
foreach (var s in styles)
{
if (s is TableStyle)
{
var style = s as TableStyle;
var border = new DocumentFormat.OpenXml.Spreadsheet.Border();
var leftBorder = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder();
var rightBorder = new DocumentFormat.OpenXml.Spreadsheet.RightBorder();
var topBorder = new DocumentFormat.OpenXml.Spreadsheet.TopBorder();
var bottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder();
leftBorder.Style = SetLineType(style.BorderLine);
rightBorder.Style = SetLineType(style.BorderLine);
topBorder.Style = SetLineType(style.BorderLine);
bottomBorder.Style = SetLineType(style.BorderLine);
leftBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
rightBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
topBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
bottomBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
border.LeftBorder = leftBorder;
border.RightBorder = rightBorder;
border.TopBorder = topBorder;
border.BottomBorder = bottomBorder;
stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(border);
var patternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill
{
BackgroundColor = new DocumentFormat.OpenXml.Spreadsheet.BackgroundColor {
Rgb = ToHexBinaryValue(style.Background)
},
ForegroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor {
Rgb = ToHexBinaryValue(style.Foreground)
},
PatternType = SetPatternType(style.PatternType)
};
var fill = new DocumentFormat.OpenXml.Spreadsheet.Fill {
PatternFill = patternFill
};
stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(fill);
DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
Val = new StringValue {
Value = s.FontName
}
},
FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
Val = s.FontSize
},
Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(s.FontColor)
}
};
stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
}
else
{
stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill());
stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border());
DocumentFormat.OpenXml.Spreadsheet.Font font;
if (s != null)
{
font = new DocumentFormat.OpenXml.Spreadsheet.Font
{
FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
Val = new StringValue {
Value = s.FontName
}
},
FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
Val = s.FontSize
},
Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(s.FontColor)
}
};
foreach (var item in s.TextStyle)
{
switch (item)
{
case TextStyleType.Bold: font.Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold {
Val = true
}; break;
case TextStyleType.Italic: font.Italic = new DocumentFormat.OpenXml.Spreadsheet.Italic {
Val = true
}; break;
case TextStyleType.Underline: font.Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline {
Val = DocumentFormat.OpenXml.Spreadsheet.UnderlineValues.Single
}; break;
case TextStyleType.Normal: break;
}
}
}
else
{
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
}
stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
}
if (s != null)
{
var cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat
{
BorderId = UInt32Value.ToUInt32(styleId),
FillId = UInt32Value.ToUInt32(styleId),
FontId = UInt32Value.ToUInt32(styleId)
};
if (s.Aligment != null)
{
var align = new Alignment()
{
TextRotation = new UInt32Value(s.Aligment.Rotation),
Horizontal = AlignmentMapper.MapHorizontalAligment(s.Aligment.HorizontalAligment),
Vertical = AlignmentMapper.MapVerticalAligment(s.Aligment.VerticalAligment)
};
cellFormat.Append(align);
}
stylesPart.Stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(cellFormat);
}
styleId = styleId + 1;
}
foreach (var x in report)
{
if (x is ComplexHeaderCell)
{
var element = x as ComplexHeaderCell;
if (Render(element, styles, shData, worksheetPart, workbookpart))
{
continue;
}
}
if (x is TextElement)
{
var element = x as TextElement;
if (Render(element, styles, shData, workbookpart))
{
continue;
}
}
if (x is TableElement)
{
var element = x as TableElement;
if (Render(element, styles, shData, workbookpart))
{
continue;
}
}
if (x is ImageElement)
{
var element = x as ImageElement;
if (Render(element, styles, shData, workbookpart))
{
continue;
}
}
}
//var dateLine = shData.AppendChild(new Spreadsheet.Row());
//dateLine.AppendChild(new Spreadsheet.Cell()
//{
// CellValue = new Spreadsheet.CellValue(_report.TimeStamp.Date.ToString()),
// DataType = Spreadsheet.CellValues.String,
// StyleIndex = 0
//});
workbookpart.Workbook.Save();
}
buffer = new byte[outputMemoryStream.Position];
outputMemoryStream.Position = 0;
outputMemoryStream.Read(buffer, 0, buffer.Length);
}
return(buffer);
}