public static void CreateNewDocument(string fileName, string filepath, DataTable dt, bool useExistingFile) { // Create a spreadsheet document by supplying the filepath. // By default, AutoSave = true, Editable = true, and Type = xlsx. FileInfo f = new FileInfo(filepath); SpreadsheetDocument spreadsheetDocument = null; WorkbookPart workbookpart = null; WorksheetPart worksheetPart = null; if (useExistingFile) { if (f.Exists) { spreadsheetDocument = SpreadsheetDocument.Open(filepath, true); } else { throw new FileNotFoundException("Import Excel file was not found!"); } // Add a blank WorksheetPart. workbookpart = spreadsheetDocument.WorkbookPart; } else { if (f.Exists) { f.Delete(); } spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document. workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); } // Add a WorksheetPart to the WorkbookPart. worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); // Add Sheets to the Workbook. worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = workbookpart.Workbook.GetFirstChild <Sheets>(); if (sheets == null) { sheets = workbookpart.Workbook.AppendChild <Sheets>(new Sheets()); } //check if sheet exists Sheet sheet = sheets.Elements <Sheet>().Where(s => s.Name == fileName).FirstOrDefault();; if (sheet != null) { sheets.RemoveChild(sheet); } sheet = null; // Get a unique ID for the new worksheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } // Append a new worksheet and associate it with the workbook. sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = fileName }; sheets.Append(sheet); //append data string cl = ""; uint row = 2; int index; Cell cell; SharedStringTablePart shareStringPart; if (spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0) { shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First(); } else { shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>(); } foreach (DataRow dr in dt.Rows) { for (int idx = 0; idx < dt.Columns.Count; idx++) { if (idx >= 26) { cl = "A" + Convert.ToString(Convert.ToChar(65 + idx - 26)); } else { cl = Convert.ToString(Convert.ToChar(65 + idx)); } if (row == 2) { index = InsertSharedStringItem(dt.Columns[idx].ColumnName, shareStringPart); cell = InsertCellInWorksheet(cl, row - 1, worksheetPart); cell.CellValue = new CellValue(index.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); } // Insert the text into the SharedStringTablePart. index = InsertSharedStringItem(Convert.ToString(dr[idx]), shareStringPart); cell = InsertCellInWorksheet(cl, row, worksheetPart); cell.CellValue = new CellValue(index.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); } row++; } workbookpart.Workbook.Save(); spreadsheetDocument.Close(); }
/// <summary> /// Iterates through each blob (file) in the folder, /// and prepares the output text that is written to the output blob. /// </summary> public static string Calculate(BlobResultSegment Bresult, IActivityLogger logger, string folderPath, ref BlobContinuationToken token) { { string output = string.Empty; logger.Write("number of blobs found: {0}", Bresult.Results.Count <IListBlobItem>()); foreach (IListBlobItem listBlobItem in Bresult.Results) { CloudBlockBlob inputBlob = listBlobItem as CloudBlockBlob; if ((inputBlob != null) && (inputBlob.Name.IndexOf("$$$.$$$") == -1)) { var filename = Environment.CurrentDirectory + "\\Book1.xlsx"; inputBlob.DownloadToFile(filename, FileMode.Create); List <string> allRows = new List <string>(); int j = 0; using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; IEnumerable <Sheet> Sheets = myDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().Where(s => s.Name == SheetName); if (Sheets.Count() == 0) { throw new ArgumentException("sheetName"); } string relationshipId = Sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)myDoc.WorkbookPart.GetPartById(relationshipId); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); int i = 1; string value; string newLine; logger.Write("Sheet is opened. "); foreach (Row r in sheetData.Elements <Row>()) { newLine = ""; List <string> currentRow = new List <string>(); if (i <FirstRow | i> LastRow) { i++; continue; } else { i++; foreach (Cell c in r.Elements <Cell>()) { if (c != null) { value = c.InnerText; if (value == "") { value = "Null"; currentRow.Add(value); j = j + 1; continue; } value = c.CellValue.Text; if (c.DataType != null) { switch (c.DataType.Value) { case CellValues.SharedString: var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { value = stringTable.SharedStringTable. ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } currentRow.Add(value); j = j + 1; } } } j = 0; newLine = String.Join(", ", currentRow.ToArray()); allRows.Add(newLine); } return(string.Join(Environment.NewLine, allRows.ToArray())); } } return(string.Empty); } return(string.Empty); } }
/// <summary> /// Function to convert a macro enabled file to a non-macro enabled file /// </summary> /// <param name="fileName">file location</param> /// <param name="app">app type</param> /// <returns></returns> public static string ConvertMacroEnabled2NonMacroEnabled(string fileName, string app) { bool fileChanged = false; string newFileName = string.Empty; string fileExtension; if (app == "Word") { fileExtension = ".docx"; using (WordprocessingDocument document = WordprocessingDocument.Open(fileName, true)) { // Access the main document part. var docPart = document.MainDocumentPart; // Look for the vbaProject part. If it is there, delete it. var vbaPart = docPart.VbaProjectPart; if (vbaPart != null) { // Delete the vbaProject part and then save the document. docPart.DeletePart(vbaPart); docPart.Document.Save(); // Change the document type to not macro-enabled. document.ChangeDocumentType(WordprocessingDocumentType.Document); // Track that the document has been changed. fileChanged = true; } } } else if (app == "PowerPoint") { fileExtension = ".pptx"; using (PresentationDocument document = PresentationDocument.Open(fileName, true)) { var docPart = document.PresentationPart; var vbaPart = docPart.VbaProjectPart; if (vbaPart != null) { docPart.DeletePart(vbaPart); docPart.Presentation.Save(); document.ChangeDocumentType(PresentationDocumentType.Presentation); fileChanged = true; } } } else { fileExtension = ".xlsx"; using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true)) { var docPart = document.WorkbookPart; var vbaPart = docPart.VbaProjectPart; if (vbaPart != null) { docPart.DeletePart(vbaPart); docPart.Workbook.Save(); document.ChangeDocumentType(SpreadsheetDocumentType.Workbook); fileChanged = true; } } } // If anything goes wrong in this file handling, // the code will raise an exception back to the caller. if (fileChanged) { // Create the new filename. newFileName = Path.ChangeExtension(fileName, fileExtension); // If it already exists, it will be deleted! if (File.Exists(newFileName)) { File.Delete(newFileName); } // Rename the file. File.Move(fileName, newFileName); } return(newFileName); }
public string[][] ReadBase(string filePath, int colLength = -1, int startRowIndex = 0, int rowLength = -1, int sheetIndex = 1) { string[][] data = null; if (string.IsNullOrEmpty(filePath) || !File.Exists(filePath)) { return(data); } this.GenSheetInfo(filePath); if (null == this.sheetInfos || this.sheetInfos.Count == 0) { return(data); } try { using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart; WorksheetPart worksheetPart = null; string rid = this.GetRid(sheetIndex); foreach (var sheetPart in workbookPart.WorksheetParts) { if (spreadsheetDoc.WorkbookPart.GetIdOfPart(sheetPart) == rid) { worksheetPart = sheetPart; } } if (null == worksheetPart) { return(data); } SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); if (null == sheetData.Elements <Row>() || sheetData.Elements <Row>().Count() == 0) { return(data); } var rows = sheetData.Elements <Row>().ToList(); if (null == rows || rows.Count == 0) { return(data); } if (rowLength == -1) { rowLength = rows.Count - startRowIndex; } if (rows.Count - startRowIndex < rowLength) { return(data); } data = new string[rowLength][]; for (int i = startRowIndex; i < rowLength + startRowIndex; i++) { var cells = rows[i].Elements <Cell>().ToList(); if (null == cells || cells.Count == 0) { return(data); } int cIndex = 0; if (colLength == -1 && i == 0) { colLength = cells.Count; } if (colLength == -1) { throw new Exception(""); } this.fieldCount = colLength; this.GenColumnMarks(); data[i - startRowIndex] = new string[colLength]; for (int j = 0; j < colLength; j++) { if (cIndex < cells.Count) { if (cells[cIndex].CellReference == null) { string errMsg = "Bad format!"; throw new OpenXMLCellReferenceNullException(errMsg); } if (this.SeparateColumnName(cells[cIndex].CellReference) == this.columnMarks[j]) { data[i - startRowIndex][j] = GetCellValue(spreadsheetDoc, cells[cIndex++]); } else { data[i - startRowIndex][j] = string.Empty; } } else { data[i - startRowIndex][j] = string.Empty; } } } } } catch (IOException io_ex) { throw io_ex; } catch (Exception ex) { throw ex; } return(data); }
public static async Task Main() { _core = new Core(); var connection = _core .StartSqlOnly( "host= localhost;Database=420_SDK;user = root; password=secretpassword;port=3306;Convert Zero Datetime = true;SslMode=none;") .Result; MicrotingDbContext dbContext = _core.DbContextHelper.GetDbContext(); QuestionSet questionSets = new QuestionSet { Name = "Test-Set" }; if (dbContext.QuestionSets.Count(x => x.Name == questionSets.Name) != 1) { await questionSets.Create(dbContext); } Language language = new Language { LanguageCode = "da", Name = "Danish" }; if (dbContext.Languages.Count(x => x.Name == "Danish") != 1) { await language.Create(dbContext); } Language dbLanguage = await dbContext.Languages.FirstOrDefaultAsync(x => x.Name == language.Name); QuestionSet dbQuestionSets = await dbContext.QuestionSets.FirstOrDefaultAsync(x => x.Name == questionSets.Name); string[] questionNames = new[] { "Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11: Er personalet opmærksomt på at involvere dine pårørende/venner (hvis du ønsker det)?", "Q12: Hvor tilfreds er du alt i alt tilfreds med dit forløb, fra du blev indlagt til du blev udskrevet?", "Q13: ...", "Enter you opinion about the question", "Enter you postalcode", "Q16" }; // 13 14 15 16 17 18 19 20 21 22 23 24 List <KeyValuePair <int, Question> > questionIds = new List <KeyValuePair <int, Question> >(); int qi = 13; foreach (var questionName in questionNames) { if (questionName != "Q13: ..." && questionName != "Q1" && questionName != "Q16") { var questionTranslation = dbContext.QuestionTranslations.SingleOrDefault(x => x.Name == questionName); if (questionTranslation == null) { Question question = new Question() { QuestionSetId = dbQuestionSets.Id, QuestionType = Constants.QuestionTypes.Smiley2 }; await question.Create(dbContext); KeyValuePair <int, Question> kvp = new KeyValuePair <int, Question>(qi, question); questionIds.Add(kvp); questionTranslation = new QuestionTranslation() { Name = questionName, QuestionId = question.Id, LanguageId = dbLanguage.Id }; await questionTranslation.Create(dbContext); } else { List <Option> options = await dbContext.Options .Where(x => x.QuestionId == questionTranslation.QuestionId).ToListAsync(); foreach (Option option in options) { List <OptionTranslation> optionTranslations = await dbContext.OptionTranslations .Where(x => x.OptionId == option.Id).ToListAsync(); option.OptionTranslationses = optionTranslations; } Question question = await dbContext.Questions.SingleAsync(x => x.Id == questionTranslation.QuestionId); questionTranslation.Question = question; questionTranslation.Question.Options = options; KeyValuePair <int, Question> kvp = new KeyValuePair <int, Question>(qi, questionTranslation.Question); questionIds.Add(kvp); } } else { var questionTranslation = dbContext.QuestionTranslations.SingleOrDefault(x => x.Name == questionName); if (questionTranslation == null) { Question question = new Question() { QuestionSetId = dbQuestionSets.Id, QuestionType = (questionName == "Q1" || questionName == "Q16") ? Constants.QuestionTypes.List : Constants.QuestionTypes.Multi }; await question.Create(dbContext); questionTranslation = new QuestionTranslation() { Name = questionName, QuestionId = question.Id, LanguageId = dbLanguage.Id }; await questionTranslation.Create(dbContext); string[] questionOptions; switch (questionName) { case "Q1": questionOptions = new[] { "Ja", "Nej" }; break; case "Q16": questionOptions = new[] { "Love it", "Like much", "Like", "Neutral", "Does not like", "Does not like at all", "Hate" }; break; default: { questionOptions = new[] { "1", "2", "3", "4", "5" }; break; } } int j = 1; foreach (string questionOption in questionOptions) { if (questionName == "Q16") { j += 10; } Option option = new Option() { QuestionId = question.Id, Weight = j, WeightValue = j }; await option.Create(dbContext); OptionTranslation optionTranslation = new OptionTranslation() { OptionId = option.Id, Name = questionOption, LanguageId = dbLanguage.Id }; await optionTranslation.Create(dbContext); } KeyValuePair <int, Question> kvp = new KeyValuePair <int, Question>(qi, question); questionIds.Add(kvp); } else { List <Option> options = await dbContext.Options .Where(x => x.QuestionId == questionTranslation.QuestionId).ToListAsync(); foreach (Option option in options) { List <OptionTranslation> optionTranslations = await dbContext.OptionTranslations .Where(x => x.OptionId == option.Id).ToListAsync(); option.OptionTranslationses = optionTranslations; } Question question = await dbContext.Questions.SingleAsync(x => x.Id == questionTranslation.QuestionId); questionTranslation.Question = question; questionTranslation.Question.Options = options; KeyValuePair <int, Question> kvp = new KeyValuePair <int, Question>(qi, questionTranslation.Question); questionIds.Add(kvp); } } qi++; } // Q13 with options // KeyValuePair<int, questions> kvp = new KeyValuePair<int, questions>(qi, questionTranslation.Question); // questionIds.Add(kvp); SurveyConfiguration surveyConfiguration = new SurveyConfiguration { QuestionSetId = dbQuestionSets.Id, Name = "Configuartion 1" }; if (dbContext.SurveyConfigurations.Count(x => x.Name == surveyConfiguration.Name) != 1) { await surveyConfiguration.Create(dbContext); } SurveyConfiguration dbSurveyConfiguration = await dbContext.SurveyConfigurations.FirstOrDefaultAsync(x => x.Name == surveyConfiguration.Name); // dbContext.question_sets questionSets = new question_sets(); Random rnd = new Random(); var document = @"/home/rene/Documents/workspace/microting/ExcelProofOfConcept/Test-data.xlsx"; using (FileStream fs = new FileStream(document, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false)) { S sheets = doc.WorkbookPart.Workbook.Sheets; foreach (E sheet in sheets) { foreach (A attr in sheet.GetAttributes()) { Console.WriteLine("{0}: {1}", attr.LocalName, attr.Value); } } WorkbookPart workbookPart = doc.WorkbookPart; WorksheetPart worksheetPart = GetWorksheetFromSheetName(workbookPart, "Senge_voksen"); var sheet1 = worksheetPart.Worksheet; // Console.WriteLine(sheet1); var cells = sheet1.Descendants <Cell>(); var rows = sheet1.Descendants <Row>(); var cols = sheet1.Descendants <Column>(); List <Column> columns = cols.ToList(); string text; var rows1 = sheet1.GetFirstChild <SheetData>().Elements <Row>(); int i = 0; var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>() .FirstOrDefault(); List <KeyValuePair <string, Site> > localSites = new List <KeyValuePair <string, Site> >(); List <KeyValuePair <string, Unit> > localUnits = new List <KeyValuePair <string, Unit> >(); //var languageId = dbContext.Languages.SingleOrDefault(x => x.Name == "da-DK"); // List<sites> localSites = new List<sites>(); // List<units> localUnits = new List<units>(); foreach (var row in rows1) { if (i > 0 && i < 553) { var cells1 = row.Elements <Cell>(); int cellNumber = 0; Answer answer = null; List <Cell> theCells = cells1.ToList(); var microtingUid = int.Parse(theCells[0].CellValue.Text); var duration = int.Parse(theCells[1].CellValue.Text); var date = stringTable.SharedStringTable.ElementAt( int.Parse(theCells[2].CellValue.Text)).InnerText; var time = stringTable.SharedStringTable.ElementAt( int.Parse(theCells[7].CellValue.Text)).InnerText; DateTime dateOfDoing = DateTime.ParseExact($"{date} {time}", "dd-MM-yyyy HH:mm:ss", null); var location = stringTable.SharedStringTable.ElementAt( int.Parse(theCells[9].CellValue.Text)).InnerText; int?sdkSiteId = null; int?sdkUnitId = null; if (localSites.Any(x => x.Key == location)) { sdkSiteId = localSites.First(x => x.Key == location).Value.Id; } else { var lookupSite = dbContext.Sites.SingleOrDefault(x => x.Name == location); if (lookupSite != null) { KeyValuePair <string, Site> pair = new KeyValuePair <string, Site>(location, lookupSite); localSites.Add(pair); sdkSiteId = lookupSite.Id; } else { Site site = new Site { Name = location, MicrotingUid = rnd.Next(1, 999999) }; await site.Create(dbContext); KeyValuePair <string, Site> pair = new KeyValuePair <string, Site>(location, site); localSites.Add(pair); sdkSiteId = site.Id; } } var unitString = theCells[11].CellValue.Text; if (localUnits.Any(x => x.Key == unitString)) { sdkUnitId = localUnits.First(x => x.Key == unitString).Value.Id; } else { var lookupUnit = dbContext.Units.SingleOrDefault(x => x.MicrotingUid.ToString() == unitString); if (lookupUnit != null) { KeyValuePair <string, Unit> pair = new KeyValuePair <string, Unit>(unitString, lookupUnit); localUnits.Add(pair); sdkUnitId = lookupUnit.Id; } else { Unit unit = new Unit { MicrotingUid = int.Parse(unitString), SiteId = sdkSiteId }; await unit.Create(dbContext); KeyValuePair <string, Unit> pair = new KeyValuePair <string, Unit>(unitString, unit); localUnits.Add(pair); sdkUnitId = unit.Id; } } answer = dbContext.Answers.SingleOrDefault(x => x.MicrotingUid == microtingUid); if (answer == null) { answer = new Answer { AnswerDuration = duration, UnitId = (int)sdkUnitId, SiteId = (int)sdkSiteId, MicrotingUid = microtingUid, FinishedAt = dateOfDoing, LanguageId = dbLanguage.Id, QuestionSetId = dbQuestionSets.Id, SurveyConfigurationId = dbSurveyConfiguration.Id }; await answer.Create(dbContext); } foreach (var cell in cells1) { if (cell == null) { Console.WriteLine("We got a null here"); } else { if (cellNumber > 12) { int questionLookupId = cellNumber; if (cellNumber > 25) { questionLookupId = 25; if (cellNumber > 29) { questionLookupId = 28; } } int?lookupOptionId = null; if (cell.DataType != null) { // if (cell.DataType.Value == CellValues.Number) // { // lookupOptionId = questionIds // .First(x => x.Key == questionLookupId).Value.Options // .SingleOrDefault(x => x.WeightValue == int.Parse(cell.CellValue.Text)).Id; if (cell.DataType.Value == CellValues.SharedString) { // if (questionLookupId != 25) // { foreach (Option option in questionIds .First(x => x.Key == questionLookupId).Value.Options) { text = stringTable.SharedStringTable.ElementAt( int.Parse(cell.CellValue.Text)).InnerText; var optionTranslation = await dbContext.OptionTranslations.SingleOrDefaultAsync(x => x.OptionId == option.Id && x.Name == text); if (optionTranslation != null) { lookupOptionId = optionTranslation.OptionId; } } } else { switch (cellNumber) { case 25: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .ToList()[0].Id; break; case 26: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .ToList()[1].Id; break; case 27: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .ToList()[2].Id; break; case 28: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .ToList()[3].Id; break; case 29: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .ToList()[4].Id; break; case 30: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .SingleOrDefault(x => x.WeightValue == int.Parse(cell.CellValue.Text)).Id; break; } } } else { if (cellNumber > 13 && cellNumber < 25) { if (cell.CellValue != null) { lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .SingleOrDefault(x => x.WeightValue == int.Parse(cell.CellValue.Text)).Id; } } else { switch (cellNumber) { case 25: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .ToList()[0].Id; break; case 26: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .ToList()[1].Id; break; case 27: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .ToList()[2].Id; break; case 28: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .ToList()[3].Id; break; case 29: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .ToList()[4].Id; break; case 30: lookupOptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .SingleOrDefault(x => x.WeightValue == int.Parse(cell.CellValue.Text)).Id; break; } } } // } // else // { // if (questionLookupId == 25) // { // lookupOptionId = questionIds.First(x => x.Key == questionLookupId) // .Value.Options.ToList()[cellNumber - 25].Id; // } // } // } // } // else // { // lookupOptionId = questionIds // .First(x => x.Key == questionLookupId).Value.Options // .SingleOrDefault(x => x.WeightValue == int.Parse(cell.CellValue.Text)).Id; // } // AnswerValue answerValue = null; if (lookupOptionId != null) { var bla = questionIds.First(y => y.Key == questionLookupId).Value.Id; answerValue = dbContext.AnswerValues .SingleOrDefault(x => x.AnswerId == answer.Id && x.QuestionId == bla && x.OptionId == lookupOptionId); } else { var bla = questionIds.First(y => y.Key == questionLookupId).Value.Id; answerValue = dbContext.AnswerValues .SingleOrDefault(x => x.AnswerId == answer.Id && x.QuestionId == bla); } if (answerValue == null) { answerValue = new AnswerValue { AnswerId = answer.Id, QuestionId = questionIds.First(x => x.Key == questionLookupId).Value.Id }; if (cell.DataType != null) { if (cell.DataType.Value == CellValues.SharedString) { if (stringTable != null) { text = stringTable.SharedStringTable.ElementAt( int.Parse(cell.CellValue.Text)).InnerText; // Console.WriteLine(text + " "); int optionId = 0; foreach (Option option in questionIds .First(x => x.Key == questionLookupId).Value.Options) { var optionTranslation = await dbContext.OptionTranslations.SingleOrDefaultAsync( x => x.OptionId == option.Id && x.Name.Equals(text)); if (optionTranslation != null) { optionId = option.Id; } } if (optionId != 0) { answerValue.Value = text; answerValue.OptionId = optionId; answerValue.QuestionId = questionIds .First(x => x.Key == questionLookupId).Value.Id; await answerValue.Create(dbContext); } else { Console.WriteLine("bla"); } } } } else { if (cellNumber > 13 && cellNumber < 25) { if (cell.CellValue != null) { text = cell.CellValue.Text; answerValue.Value = text; answerValue.QuestionId = questionIds .First(x => x.Key == questionLookupId).Value.Id; answerValue.OptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options .SingleOrDefault(x => x.WeightValue == int.Parse(text)).Id; await answerValue.Create(dbContext); // Console.WriteLine(cell.CellValue.Text); } } else { if (cell.CellValue != null) { if (int.Parse(cell.CellValue.Text) == 1) { answerValue.Value = "1"; answerValue.QuestionId = questionIds .First(x => x.Key == questionLookupId).Value.Id; switch (cellNumber) { case 25: answerValue.OptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options.ToList()[0].Id; break; case 26: answerValue.OptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options.ToList()[1].Id; break; case 27: answerValue.OptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options.ToList()[2].Id; break; case 28: answerValue.OptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options.ToList()[3].Id; break; case 29: answerValue.OptionId = questionIds .First(x => x.Key == questionLookupId).Value.Options.ToList()[4].Id; break; } await answerValue.Create(dbContext); } } } } } } } cellNumber++; } } Console.WriteLine($"ROW {i}"); i++; } } } // Q1 int q1Id = 1; int q2Id = 2; int q3Id = 3; int q4Id = 4; int q5Id = 5; int q6Id = 6; int q7Id = 7; int q8Id = 8; int q9Id = 9; int q10Id = 10; int q11Id = 11; int q12Id = 12; int q13Id = 13; int optionJaId = 1; int optionNejId = 2; int option100q2Id = 3; int option75q2Id = 4; int option50q2Id = 5; int option25q2Id = 6; int option0q2Id = 7; int option999q2Id = 8; int option100q3Id = 9; int option75q3Id = 10; int option50q3Id = 11; int option25q3Id = 12; int option0q3Id = 13; int option999q3Id = 14; int option100q4Id = 15; int option75q4Id = 16; int option50q4Id = 17; int option25q4Id = 18; int option0q4Id = 19; int option999q4Id = 20; int option100q5Id = 21; int option75q5Id = 22; int option50q5Id = 23; int option25q5Id = 24; int option0q5Id = 25; int option999q5Id = 26; int option100q6Id = 27; int option75q6Id = 28; int option50q6Id = 29; int option25q6Id = 30; int option0q6Id = 31; int option999q6Id = 32; int option100q7Id = 33; int option75q7Id = 34; int option50q7Id = 35; int option25q7Id = 36; int option0q7Id = 37; int option999q7Id = 38; int option100q8Id = 39; int option75q8Id = 40; int option50q8Id = 41; int option25q8Id = 42; int option0q8Id = 43; int option999q8Id = 44; int option100q9Id = 45; int option75q9Id = 46; int option50q9Id = 47; int option25q9Id = 48; int option0q9Id = 49; int option999q9Id = 50; int option100q10Id = 51; int option75q10Id = 52; int option50q10Id = 53; int option25q10Id = 54; int option0q10Id = 55; int option999q10Id = 56; int option100q11Id = 57; int option75q11Id = 58; int option50q11Id = 59; int option25q11Id = 60; int option0q11Id = 61; int option999q11Id = 62; int option100q12Id = 63; int option75q12Id = 64; int option50q12Id = 65; int option25q12Id = 66; int option0q12Id = 67; int option999q12Id = 68; int optionq13_1Id = 69; int optionq13_2Id = 70; int optionq13_3Id = 71; int optionq13_4Id = 72; int optionq13_5Id = 73; Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == optionJaId) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == optionNejId) == 133); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q1Id) == 552); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q2Id) == 5); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q2Id) == 7); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q2Id) == 14); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q2Id) == 112); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q2Id) == 275); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q2Id) == 6); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q2Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q3Id) == 15); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q3Id) == 8); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q3Id) == 44); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q3Id) == 144); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q3Id) == 201); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q3Id) == 7); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q3Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q4Id) == 13); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q4Id) == 17); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q4Id) == 78); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q4Id) == 123); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q4Id) == 176); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q4Id) == 12); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q4Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q5Id) == 16); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q5Id) == 18); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q5Id) == 49); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q5Id) == 135); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q5Id) == 188); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q5Id) == 13); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q5Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q6Id) == 21); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q6Id) == 23); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q6Id) == 61); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q6Id) == 131); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q6Id) == 160); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q6Id) == 23); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q6Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q7Id) == 13); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q7Id) == 8); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q7Id) == 57); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q7Id) == 116); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q7Id) == 216); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q7Id) == 9); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q7Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q8Id) == 35); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q8Id) == 27); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q8Id) == 98); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q8Id) == 108); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q8Id) == 124); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q8Id) == 27); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q8Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q9Id) == 19); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q9Id) == 23); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q9Id) == 51); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q9Id) == 107); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q9Id) == 213); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q9Id) == 6); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q9Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q10Id) == 16); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q10Id) == 10); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q10Id) == 66); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q10Id) == 116); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q10Id) == 186); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q10Id) == 25); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q10Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q11Id) == 11); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q11Id) == 8); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q11Id) == 41); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q11Id) == 111); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q11Id) == 211); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q11Id) == 37); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q11Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option0q12Id) == 12); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option25q12Id) == 9); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option50q12Id) == 58); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option75q12Id) == 126); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option100q12Id) == 187); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == option999q12Id) == 27); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q12Id) == 419); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == optionq13_1Id) == 289); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q13Id) == 1383); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == optionq13_2Id) == 273); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q13Id) == 1383); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == optionq13_3Id) == 281); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q13Id) == 1383); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == optionq13_4Id) == 271); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q13Id) == 1383); Debug.Assert(dbContext.AnswerValues.Count(x => x.OptionId == optionq13_5Id) == 269); Debug.Assert(dbContext.AnswerValues.Count(x => x.QuestionId == q13Id) == 1383); Console.WriteLine("we are done"); }
public void ReadSheet <T>(Stream stream, ReadSheetOptions <T> option) where T : class, new() { Inspector.NotNull(stream, "Excel文件流不能为空"); Inspector.NotNull(option, $"{nameof(ReadSheetOptions<T>)} can not be null"); //匹配SheetName var sheetName = ""; { var sheetNames = this.GetSheetNames(stream, false); Inspector.Validation(option.ReadWay == ReadWay.SheetIndex && option.SheetIndex > sheetNames.Count(), $"指定的SheetIndex {option.SheetIndex} 无效,实际只存在{sheetNames.Count()}个Sheet"); Inspector.Validation(option.ReadWay == ReadWay.SheetName && !sheetNames.Contains(option.SheetName), $"指定的SheetName {option.SheetName} 不存在"); sheetName = option.ReadWay switch { ReadWay.SheetIndex => sheetNames.ElementAt(option.SheetIndex - 1), ReadWay.SheetName => option.SheetName }; } //Excel中的表头列与实体类中字段映射(index:集合中元素的位置,cellRef:单元格的A1 B1中的A B这种) var fieldLoc = new List <(int index, string excelField, string cellRef, string classField, bool allowNull, PropertyInfo prop)>(); { var props = ReflectionHelper.NewInstance.GetSortedReadProps <T>().Select(t => t.prop).ToList(); for (int index = 0; index < props.Count(); index++) { var attribute = props[index].GetCustomAttribute <ExcelKitAttribute>(); fieldLoc.Add((index, attribute.Desc, "", attribute.Code ?? props[index].Name, attribute.AllowNull, props[index])); } } using (var sheetDoc = SpreadsheetDocument.Open(stream, false)) { WorkbookPart workbookPart = sheetDoc.WorkbookPart; //1.目标Sheet的Rid是否存在 string rId = workbookPart.Workbook.Sheets?.Cast <Sheet>()?.FirstOrDefault(t => t.Name.Value == sheetName)?.Id?.Value; Inspector.NotNullOrWhiteSpace(rId, $"不存在名为:{sheetName} 的Sheet"); SharedStringTablePart shareStringPart; if (workbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0) { shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First(); } else { shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>(); } string[] shareStringItemValues = shareStringPart.GetItemValues().ToArray(); //2.反转Sheet顺序 foreach (var workSheetPart in workbookPart.WorksheetParts?.Reverse()) { //是否是指定Sheet的Rid string partRelationshipId = workbookPart.GetIdOfPart(workSheetPart); if (partRelationshipId != rId) { continue; } //读取失败的原始数据信息 (Dictionary <string, object> odata, List <(string rowIndex, string columnName, string cellValue, string errorMsg)> failInfos)failRowData = (new Dictionary <string, object>(), new List <(string rowIndex, string columnName, string cellValue, string errorMsg)>()); //创建Reader OpenXmlReader reader = OpenXmlReader.Create(workSheetPart); //工具类实例 var reflection = ReflectionHelper.NewInstance; while (reader.Read()) { if (reader.ElementType == typeof(Worksheet)) { reader.ReadFirstChild(); } if (reader.ElementType == typeof(Row)) { var row = (Row)reader.LoadCurrentElement(); //3.读取表头列,匹配字段信息 if (row.RowIndex == option.HeadRow) { foreach (Cell cell in row.Elements <Cell>()) { if (cell.CellReference != null && cell.CellReference.HasValue) { //excel中的表头列字段 string outerCode = cell.GetValue(shareStringItemValues); if (fieldLoc.Exists(t => t.excelField == outerCode)) { var fieldInfo = fieldLoc.FirstOrDefault(t => t.excelField == outerCode); fieldInfo.cellRef = StringHelper.RemoveNumber(cell.CellReference); fieldLoc[fieldInfo.index] = fieldInfo; } } } //实体上定义了ExcelKit特性的字段未在Excel中匹配到 var unMatchedField = fieldLoc.Where(t => string.IsNullOrWhiteSpace(t.cellRef)); if (unMatchedField.Count() > 0) { var obj = unMatchedField.FirstOrDefault(); var msg = $"{typeof(T).Name}中的字段{obj.classField}特性上指定的Desc:{obj.excelField} 未在Excel列头中匹配到"; throw new ExcelKitException(msg); } continue; } if (row.RowIndex < option.DataStartRow) { continue; } if (option.DataEndRow.HasValue && row.RowIndex > option.DataEndRow) { break; } //读取到的每行数据 T model = new T(); //excel原始数据 failRowData.odata.Clear(); //失败信息 failRowData.failInfos.Clear(); //是否读取成功 var readSuc = true; //4. row.Elements<Cell>()获取出来的会自动跳过为空的单元格 foreach (Cell cell in row.Elements <Cell>()) { //4.1 跳过cell引用为空的 if (cell.CellReference == null || !cell.CellReference.HasValue) { continue; } //4.2 当前循环的cell列位置(不含数字) var loopCellRef = StringHelper.RemoveNumber(cell.CellReference); //不存在或匹配列信息不一致的跳过 var fieldInfo = fieldLoc.FirstOrDefault(t => t.cellRef.Equals(loopCellRef, StringComparison.OrdinalIgnoreCase)); if (fieldInfo == (0, null, null, null, false, null) || !loopCellRef.Equals(fieldInfo.cellRef, StringComparison.OrdinalIgnoreCase)) { continue; } //Excel中读取到的值 string value = cell.GetValue(shareStringItemValues); Inspector.Validation(!fieldInfo.allowNull && string.IsNullOrWhiteSpace(value), $"Excel中列 {fieldInfo.excelField} 为必填项"); try { failRowData.odata.Add(fieldInfo.excelField, value); reflection.SetValue(fieldInfo.prop, ref model, value, fieldInfo.allowNull); } catch (Exception ex) { readSuc = false; failRowData.failInfos.Add((row.RowIndex, fieldInfo.excelField, value?.ToString(), ex.Message)); } } //读取成功执行 if (readSuc) { option.SucData?.Invoke(model, row.RowIndex.Value); } else { option.FailData?.Invoke(failRowData.odata, failRowData.failInfos); } } } } sheetDoc.Close(); } } }
public string[] ReadFields(string filePath) { string[] fields = null; if (string.IsNullOrEmpty(filePath) || !File.Exists(filePath)) { return(fields); } this.GenSheetInfo(filePath); try { using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart; WorksheetPart worksheetPart = null; string rid = this.GetRid(OXExcelManager.SheetIndex); foreach (var sheetPart in workbookPart.WorksheetParts) { if (spreadsheetDoc.WorkbookPart.GetIdOfPart(sheetPart) == rid) { worksheetPart = sheetPart; } } if (null == worksheetPart) { return(fields); } SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); if (null == sheetData.Elements <Row>() || sheetData.Elements <Row>().Count() == 0) { return(fields); } var rows = sheetData.Elements <Row>().ToList(); if (null == rows || rows.Count == 0 || null == rows[0].Elements <Cell>() || rows[0].Elements <Cell>().Count() == 0) { return(fields); } var cells = rows[0].Elements <Cell>().ToList(); this.fieldCount = cells.Count; this.GenColumnMarks(); fields = new string[this.fieldCount]; for (int j = 0; j < this.fieldCount; j++) { fields[j] = GetCellValue(spreadsheetDoc, cells[j]); } } } catch (IOException io_ex) { throw io_ex; } catch (Exception ex) { throw ex; } return(fields); }
/// <summary> /// Edit TimeLine attributes /// </summary> /// <param name="filePath">Editing target file path</param> /// <param name="log">Logger</param> public void EditAttributes(string filePath, VerifiableLog log) { using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true)) { X15.Timeline timeline = null; //ShowTimeLevel timeline = GetTimeLine(package.WorkbookPart, this.TileLine01); timeline.ShowTimeLevel = true; log.Pass("Set \"ShowTimeLevel\" attribute, timeline control name is \"{0}\". set value is true.", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine02); timeline.ShowTimeLevel = false; log.Pass("Set \"ShowTimeLevel\" attribute. timeline control name is \"{0}\". set value is false.", timeline.Name); //Cache timeline = GetTimeLine(package.WorkbookPart, this.TileLine03); timeline.Cache = "NativeTimeline_Date"; log.Pass("Set \"Cache\" attribute. timeline control name is \"{0}\". set value is \"NativeTimeline_Date\".", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine04); timeline.Cache = "NativeTimeline_DeliveryDate"; log.Pass("Set \"Cache\" attribute. timeline control name is \"{0}\". set value is \"NativeTimeline_DeliveryDate\".", timeline.Name); //ShowSelectionLabel timeline = GetTimeLine(package.WorkbookPart, this.TileLine05); timeline.ShowSelectionLabel = true; log.Pass("Set \"ShowSelectionLabel\" attribute. timeline control name is \"{0}\". set value is true.", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine06); timeline.ShowSelectionLabel = false; log.Pass("Set \"ShowSelectionLabel\" attribute. timeline control name is \"{0}\". set value is false.", timeline.Name); //ShowHeader timeline = GetTimeLine(package.WorkbookPart, this.TileLine07); timeline.ShowHeader = true; log.Pass("Set \"ShowHeader\" attribute. timeline control name is \"{0}\". set value is true.", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine08); timeline.ShowHeader = false; log.Pass("Set \"ShowHeader\" attribute. timeline control name is \"{0}\". set value is false.", timeline.Name); //Style timeline = GetTimeLine(package.WorkbookPart, this.TileLine09); timeline.Style = "TimeSlicerStyleLight2"; log.Pass("Set \"Style\" attribute. timeline control name is \"{0}\". set value is \"TimeSlicerStyleLight2\".", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine10); timeline.Style = null; log.Pass("Set \"Style\" attribute. timeline control name is \"{0}\". set value is null.", timeline.Name); //Caption timeline = GetTimeLine(package.WorkbookPart, this.TileLine11); timeline.Caption = null; log.Pass("Set \"Caption\" attribute. timeline control name is \"{0}\". set value is null.", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine12); timeline.Caption = "0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789"; log.Pass("Set \"Caption\" attribute. timeline control name is \"{0}\". set value is \"0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789\".", timeline.Name); //ScrollPosition timeline = GetTimeLine(package.WorkbookPart, this.TileLine13); timeline.ScrollPosition = DateTime.Parse("1900-01-01"); log.Pass("Set \"ScrollPosition\" attribute. timeline control name is \"{0}\". set value is \"1900-01-01\".", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine14); timeline.ScrollPosition = DateTime.Parse("2010-12-31"); log.Pass("Set \"ScrollPosition\" attribute. timeline control name is \"{0}\". set value is \"2010-12-31\".", timeline.Name); //Level timeline = GetTimeLine(package.WorkbookPart, this.TileLine15); timeline.Level.Value = 0U; log.Pass("Set \"ScrollPosition\" attribute. timeline control name is \"{0}\". set value is 0(Years).", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine16); timeline.Level.Value = 1U; log.Pass("Set \"ScrollPosition\" attribute. timeline control name is \"{0}\". set value is 1(Quarters).", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine17); timeline.Level.Value = 2U; log.Pass("Set \"ScrollPosition\" attribute. timeline control name is \"{0}\". set value is 2(Months).", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine18); timeline.Level.Value = 3U; log.Pass("Set \"ScrollPosition\" attribute. timeline control name is \"{0}\". set value is 3(Days).", timeline.Name); //ShowHorizontalScrollbar timeline = GetTimeLine(package.WorkbookPart, this.TileLine19); timeline.ShowHorizontalScrollbar = true; log.Pass("Set \"ShowHorizontalScrollbar\" attribute. timeline control name is \"{0}\". set value is true.", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine20); timeline.ShowHorizontalScrollbar = false; log.Pass("Set \"ShowHorizontalScrollbar\" attribute. timeline control name is \"{0}\". set value is false.", timeline.Name); //DefaultTimelineStyle attribute in TimelineStyle element X15.TimelineStyles timelineStyles = package.WorkbookPart.WorkbookStylesPart.Stylesheet.StylesheetExtensionList.Descendants <X15.TimelineStyles>().Single(); timelineStyles.DefaultTimelineStyle.Value = this.timelineStyleName2; log.Pass("Set DefaultTimelineStyle attribute value in \"X15.TimelineStyles\" element. Set value is [{0}].", this.timelineStyleName2); } }
private bool ExportToExcel(DataTable table, string sFilePath, string sSheetName = "") { try { SpreadsheetDocument workbook; if (sSheetName == "") { sSheetName = table.TableName; } if (File.Exists(sFilePath)) { workbook = SpreadsheetDocument.Open(sFilePath, true); } else { workbook = SpreadsheetDocument.Create(sFilePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook); WorkbookPart workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new Workbook(); workbook.WorkbookPart.Workbook.Sheets = new Sheets(); } uint sheetId = 1; var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); var sheetData = new SheetData(); sheetPart.Worksheet = new Worksheet(sheetData); Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); Sheet oSheet = sheets.Elements <Sheet>().Where(s => s.Name == sSheetName).FirstOrDefault(); if (oSheet != null) { oSheet.Remove(); } if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sSheetName }; sheets.Append(sheet); Row headerRow = new Row(); List <string> columns = new List <string>(); foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { Row newRow = new Row(); foreach (String col in columns) { Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(dsrow[col].ToString()); newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } workbook.Close(); return(true); } catch (Exception ex) { if (ShowMessage) { Reporter.ToUser(eUserMsgKeys.ExportExcelFileFailed, ex.Message.ToString()); } } return(false); }
/// <summary> /// Verify the TileLine attributes value. /// </summary> /// <param name="filePath">Target file path</param> /// <param name="log">Logger</param> public void VerifyEditedAttribute(string filePath, VerifiableLog log) { using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, false)) { X15.Timeline timeline = null; //verify ShowTimeLevel timeline = GetTimeLine(package.WorkbookPart, this.TileLine01); log.Verify(timeline.ShowTimeLevel == true, "Timeline control in \"ShowTimeLevel\" attribute value is Verify. Name=[{0}],Value=[true].", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine02); log.Verify(timeline.ShowTimeLevel == false, "Timeline control in \"ShowTimeLevel\" attribute value is Verify. Name=[{0}],Value=[false].", timeline.Name); //Verify Cache timeline = GetTimeLine(package.WorkbookPart, this.TileLine03); log.Verify(timeline.Cache == "NativeTimeline_Date", "Timeline control in Cache attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.Cache); timeline = GetTimeLine(package.WorkbookPart, this.TileLine04); log.Verify(timeline.Cache == "NativeTimeline_DeliveryDate", "Timeline control in Cache attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.Cache); //Verify ShowSelectionLabel timeline = GetTimeLine(package.WorkbookPart, this.TileLine05); log.Verify(timeline.ShowSelectionLabel, "Timeline control in \"ShowSelectionLabel\" attribute value is Verify. Name=[{0}],Value=[true].", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine06); log.Verify(timeline.ShowSelectionLabel == false, "Timeline control in \"ShowSelectionLabel\" attribute value is Verify. Name=[{0}],Value=[false].", timeline.Name); //Verify ShowHeader timeline = GetTimeLine(package.WorkbookPart, this.TileLine07); log.Verify(timeline.ShowHeader == true, "Timeline control in \"ShowHeader\" attribute value is Verify. Name=[{0}],Value=[true].", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine08); log.Verify(timeline.ShowHeader == false, "Timeline control in \"ShowHeader\" attribute value is Verify. Name=[{0}],Value=[false].", timeline.Name); //Verify Style timeline = GetTimeLine(package.WorkbookPart, this.TileLine09); log.Verify(timeline.Style == "TimeSlicerStyleLight2", "Timeline control in \"Style\" attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.Style); timeline = GetTimeLine(package.WorkbookPart, this.TileLine10); log.Verify(timeline.Style == null, "Timeline control in \"Style\" attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.Style); //Verify Caption timeline = GetTimeLine(package.WorkbookPart, this.TileLine11); log.Verify(timeline.Caption == null, "Timeline control in \"Caption\" attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.Caption); timeline = GetTimeLine(package.WorkbookPart, this.TileLine12); log.Verify(timeline.Caption == "0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789", "Timeline control in \"Caption\" attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.Caption); //Verify ScrollPosition timeline = GetTimeLine(package.WorkbookPart, this.TileLine13); log.Verify(timeline.ScrollPosition.Value == DateTime.Parse("1900-01-01"), "Timeline control in \"ScrollPosition\" attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.ScrollPosition); timeline = GetTimeLine(package.WorkbookPart, this.TileLine14); log.Verify(timeline.ScrollPosition.Value == DateTime.Parse("2010-12-31"), "Timeline control in \"ScrollPosition\" attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.ScrollPosition); //Verify Level timeline = GetTimeLine(package.WorkbookPart, this.TileLine15); log.Verify(timeline.Level == 0U, "Timeline control in \"Level\" attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.Level); timeline = GetTimeLine(package.WorkbookPart, this.TileLine16); log.Verify(timeline.Level == 1U, "Timeline control in \"Level\" attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.Level); timeline = GetTimeLine(package.WorkbookPart, this.TileLine17); log.Verify(timeline.Level == 2U, "Timeline control in \"Level\" attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.Level); timeline = GetTimeLine(package.WorkbookPart, this.TileLine18); log.Verify(timeline.Level == 3U, "Timeline control in \"Level\" attribute value is Verify. Name=[{0}],Value=[{1}].", timeline.Name, timeline.Level); //Verify ShowHorizontalScrollbar timeline = GetTimeLine(package.WorkbookPart, this.TileLine19); log.Verify(timeline.ShowHorizontalScrollbar, "Timeline control in \"ShowHorizontalScrollbar\" attribute value is Verify. Name=[{0}],Value=[true].", timeline.Name); timeline = GetTimeLine(package.WorkbookPart, this.TileLine20); log.Verify(timeline.ShowHorizontalScrollbar == false, "Timeline control in \"ShowHorizontalScrollbar\" attribute value is Verify. Name=[{0}],Value=[false].", timeline.Name); //Verify DefaultTimelineStyle attribute in TimelineStyle element X15.TimelineStyles timelineStyles = package.WorkbookPart.WorkbookStylesPart.Stylesheet.StylesheetExtensionList.Descendants <X15.TimelineStyles>().Single(); log.Verify(timelineStyles.DefaultTimelineStyle == this.timelineStyleName2, "Verify \"DefaultTimelineStyle\" attribute. Value=[{0}]", timelineStyles.DefaultTimelineStyle); } }
/// <summary> /// Delete TimeLine elements and related elements /// </summary> /// <param name="filePath">Deleting target file path</param> /// <param name="log">Logger</param> public void DeleteElements(string filePath, VerifiableLog log) { using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true)) { // 1)TimeLineCacheParts delete package.WorkbookPart.DeleteParts <TimeLineCachePart>(package.WorkbookPart.TimeLineCacheParts); log.Pass("TimeLineCacheParts delete"); // 2)"timelineCacheRefs" element inner element is delete(="ext" element delete) foreach (X15.TimelineCacheReference timelineCacheReference in package.WorkbookPart.RootElement.Descendants <X15.TimelineCacheReference>()) { timelineCacheReference.Remove(); } log.Pass("\"timelineCacheRef\" element delete"); package.WorkbookPart.RootElement.Descendants <X15.TimelineCacheReferences>().Single().Remove(); log.Pass("\"timelineCacheRefs\" element delete"); // 3)"timelineRefs" element inner element is delete(="ext" element delete) foreach (WorksheetPart worksheetPart in package.WorkbookPart.WorksheetParts) { foreach (var timelineReference in worksheetPart.RootElement.Descendants <X15.TimelineReference>().Reverse()) { timelineReference.Remove(); } foreach (var timelineRefs in worksheetPart.RootElement.Descendants <X15.TimelineReferences>()) { timelineRefs.Remove(); } } log.Pass("\"timelineRef\" element delete"); log.Pass("\"timelineRefs\" element delete"); // 4)TimeLineParts delete foreach (WorksheetPart worksheetPart in package.WorkbookPart.WorksheetParts) { if (worksheetPart.TimeLineParts != null) { worksheetPart.DeleteParts <TimeLinePart>(worksheetPart.TimeLineParts); } } log.Pass("TimeLineParts delete"); // 5)"timeslicer" element inner element is delete(="twoCellAnchor" element delete) foreach (WorksheetPart worksheetPart in package.WorkbookPart.WorksheetParts) { if (worksheetPart.DrawingsPart != null) { foreach (Xdr.TwoCellAnchor twoCellAnchor in worksheetPart.DrawingsPart.WorksheetDrawing.Reverse()) { var children = twoCellAnchor.Descendants <Tsle.TimeSlicer>(); if (children.Count() > 0) { twoCellAnchor.Remove(); log.Pass("TimeSlicer element is detected and the parent element is removed"); } } } } } }
public static DataTable ExcelWorksheetToDataTable(string pathFilename, string worksheetName) { DataTable dt = new DataTable(worksheetName); using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathFilename, false)) { // Find the sheet with the supplied name, and then use that // Sheet object to retrieve a reference to the first worksheet. Sheet theSheet = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == worksheetName).FirstOrDefault(); if (theSheet == null) { throw new Exception("Couldn't find the worksheet: " + worksheetName); } // Retrieve a reference to the worksheet part. WorksheetPart wsPart = (WorksheetPart)(document.WorkbookPart.GetPartById(theSheet.Id)); Worksheet workSheet = wsPart.Worksheet; string dimensions = workSheet.SheetDimension.Reference.InnerText; // Get the dimensions of this worksheet, eg "B2:F4" int numOfColumns = 0; int numOfRows = 0; CalculateDataTableSize(dimensions, ref numOfColumns, ref numOfRows); System.Diagnostics.Trace.WriteLine(string.Format("The worksheet \"{0}\" has dimensions \"{1}\", so we need a DataTable of size {2}x{3}.", worksheetName, dimensions, numOfColumns, numOfRows)); SheetData sheetData = workSheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); string[,] cellValues = new string[numOfColumns, numOfRows]; int colInx = 0; int rowInx = 0; string value = ""; SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart; // Iterate through each row of OpenXML data foreach (Row row in rows) { DataRow dataRow = dt.NewRow(); for (int i = 0; i < row.Descendants <Cell>().Count(); i++) { // *DON'T* assume there's going to be one XML element for each item in each row... Cell cell = row.Descendants <Cell>().ElementAt(i); if (cell.CellValue == null || cell.CellReference == null) { continue; // eg when an Excel cell contains a blank string } // Convert this Excel cell's CellAddress into a 0-based offset into our array (eg "G13" -> [6, 12]) colInx = GetColumnIndexByName(cell.CellReference); // eg "C" -> 2 (0-based) rowInx = GetRowIndexFromCellAddress(cell.CellReference); // Fetch the value in this cell value = cell.CellValue.InnerXml; if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) { value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText; } cellValues[colInx, rowInx] = value; } dt.Rows.Add(dataRow); } // Copy the array of strings into a DataTable for (int col = 0; col < numOfColumns; col++) { dt.Columns.Add("Column_" + col.ToString()); } for (int row = 0; row < numOfRows; row++) { for (int col = 0; col < numOfColumns; col++) { DataRow dataRow = dt.NewRow(); dataRow.SetField(col, cellValues[col, row]); dt.Rows.Add(dataRow); } } #if DEBUG // Write out the contents of our DataTable to the Output window (for debugging) string str = ""; for (rowInx = 0; rowInx < maxNumOfRows; rowInx++) { for (colInx = 0; colInx < maxNumOfColumns; colInx++) { object val = dt.Rows[rowInx].ItemArray[colInx]; str += (val == null) ? "" : val.ToString(); str += "\t"; } str += "\n"; } System.Diagnostics.Trace.WriteLine(str); #endif return(dt); } }
public static DataTable ReadAsDataTable(string fileName) { DataTable dt = new DataTable(); using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, false)) { WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart; IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId); Worksheet workSheet = worksheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); int cellIndex = 0; foreach (Cell cell in rows.ElementAt(0)) { if (cellIndex == 7) { dt.Columns.Add("DateFrom", typeof(DateTime)); } else if (cellIndex == 8) { dt.Columns.Add("DateTo", typeof(DateTime)); } else { dt.Columns.Add(GetCellValue(spreadSheetDocument, cell, cellIndex)); } cellIndex++; } cellIndex = 0; int rowIndex = 0; foreach (Row row in rows) //this will also include your header row... { 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 = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference)); cellColumnIndex--; //zero based index if (columnIndex < cellColumnIndex) { do { if ((columnIndex == 7 || columnIndex == 8) && rowIndex > 0) { tempRow[columnIndex] = DBNull.Value; } else { tempRow[columnIndex] = ""; //Insert blank data here; } columnIndex++; }while (columnIndex < cellColumnIndex); } if ((columnIndex == 7 || columnIndex == 8) && rowIndex > 0) { string dateValue = GetCellValue(spreadSheetDocument, cell, cellIndex); tempRow[columnIndex] = DateTime.FromOADate(double.Parse(dateValue)); } else if (rowIndex > 0) { tempRow[columnIndex] = GetCellValue(spreadSheetDocument, cell, cellIndex); } cellIndex++; columnIndex++; } dt.Rows.Add(tempRow); rowIndex++; } } dt.Rows.RemoveAt(0); return(dt); }
public override void Generate(TemplateReportVm model, string outputFilePath) { ImageResizer.Resize(LogoFilePath, LogoResized, LogoWidth, LogoHeight, true); File.Copy(TemplateFile, outputFilePath, true); byte[] byteArray = File.ReadAllBytes(outputFilePath); using (MemoryStream stream = new MemoryStream()) { stream.Write(byteArray, 0, byteArray.Length); using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, true)) { spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; // Do work here IEnumerable <Sheet> sheets = spreadSheet.WorkbookPart.Workbook .GetFirstChild <Sheets>().Elements <Sheet>() .Where(s => s.Name == "TrackingReport") .ToArray(); if (!sheets.Any()) { throw new AdsException("No sheets"); } string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(relationshipId); #region First Page ExcelHelper.AddImage(worksheetPart, LogoResized, 6, 1); Cell cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "L", 2); cell.CellValue = new CellValue(DateTime.Now.ToString(StringConstants.DateFormatSlashes)); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 10); cell.CellValue = new CellValue(model.StartDate); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 11); cell.CellValue = new CellValue(model.SubjectLine); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 13); cell.CellValue = new CellValue(model.FromLine); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 14); cell.CellValue = new CellValue(model.WhiteLabel); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 16); cell.CellValue = new CellValue(model.OrderNumber); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 17); cell.CellValue = new CellValue(model.CampaignName); cell.DataType = new EnumValue <CellValues>(CellValues.String); if (!string.IsNullOrEmpty(model.IoNumber) && !model.IoNumber.EndsWith("RDP")) { uint rowNumber = 18; foreach (var segment in model.Segments) { cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", rowNumber); cell.CellValue = new CellValue(segment.SegmentNumber); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "D", rowNumber); cell.CellValue = new CellValue(segment.SegmentDataFileUrl); cell.DataType = new EnumValue <CellValues>(CellValues.String); rowNumber++; } } // right side cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "L", 6); cell.CellValue = new CellValue(model.Quantity); cell.DataType = new EnumValue <CellValues>(CellValues.Number); // key stats cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 23); cell.CellValue = new CellValue(model.Quantity); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "H", 26); cell.CellValue = new CellValue(model.Opened); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "H", 29); cell.CellValue = new CellValue(model.Clicked); cell.DataType = new EnumValue <CellValues>(CellValues.Number); // Shared and Un-sub stats if (Template.Equals(ReportTemplate.Tracking3.ToString())) { cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "H", 32); cell.CellValue = new CellValue(model.Unsub); cell.DataType = new EnumValue <CellValues>(CellValues.Number); } #endregion uint start = 39; #region Second Page if (Template.Equals(ReportTemplate.Tracking4.ToString())) { start = 71; cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 39); cell.CellValue = new CellValue(model.SubjectLine); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 40); cell.CellValue = new CellValue(model.FromLine); cell.DataType = new EnumValue <CellValues>(CellValues.String); if (File.Exists(ScreenshotFilePath)) { ExcelHelper.AddImage(worksheetPart, ScreenshotFilePath, 41, 5); } } #endregion #region Third Page foreach (var vm in model.PerLink) { PopulateRowTemplate(worksheetPart.Worksheet, vm, start); start++; } cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "A", start); cell.CellValue = new CellValue("Total:"); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "L", start); cell.CellValue = new CellValue(model.Clicked); cell.DataType = new EnumValue <CellValues>(CellValues.Number); #endregion worksheetPart.Worksheet.Save(); spreadSheet.Close(); } File.WriteAllBytes(outputFilePath, stream.ToArray()); } }
public bool UploadLookups(int enterpriseId, int userId, IFormsRepository formsRepo, string excelFileName) { def_LookupMaster lookupMaster = formsRepo.GetLookupMastersByLookupCode("ADAP_CLINIC"); if (lookupMaster == null) { throw new Exception("Cannot find lookup master: code ADAP_CLINIC"); } MarkAllLookupsInactive(lookupMaster.lookupMasterId, enterpriseId, formsRepo); int dataStartingRow = 7; Workbook workBook; SharedStringTable sharedStrings; IEnumerable <Sheet> workSheets; WorksheetPart recSheet; try { //Excel.Application xlApp; //Excel.Workbook xlWorkBook; //Excel.Worksheet xlWorkSheet; //Excel.Range range; //xlApp = new Excel.Application(); //xlWorkBook = xlApp.Workbooks.Open(excelFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //range = xlWorkSheet.UsedRange; //for (int rCnt = dataStartingRow; rCnt <= range.Rows.Count; rCnt++) //{ // // Create string array for row to pass to UpdateLookup function // string[] dataValues = new string[range.Columns.Count]; // int index = 0; // for (int cCnt = 1; cCnt <= range.Columns.Count; cCnt++) // { // string str = (range.Cells[rCnt, cCnt] as Excel.Range).Text.ToString(); // dataValues[index] = str; // index++; // } // if (arrayNotEmpty(dataValues)) // { // UpdateLookups(enterpriseId, dataValues, formsRepo); // } //} //xlWorkBook.Close(true, null, null); //xlApp.Quit(); //return true; //Declare helper variables. string recID; //Open the Excel workbook. using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFileName, true)) { //References to the workbook and Shared String Table. workBook = document.WorkbookPart.Workbook; workSheets = workBook.Descendants <Sheet>(); sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable; //Reference to Excel Worksheet with Recipient data. //recID = workSheets.First(s => s.Name == strWorkSheetName).Id; recID = workSheets.First().Id; recSheet = (WorksheetPart)document.WorkbookPart.GetPartById(recID); //LINQ query to skip first row with column names. IEnumerable <Row> dataRows = from row in recSheet.Worksheet.Descendants <Row>() where row.RowIndex >= dataStartingRow select row; // Count the number of columns for headers. IEnumerable <Row> templateHeader = from row in recSheet.Worksheet.Descendants <Row>() where row.RowIndex == (dataStartingRow - 1) select row; int numCols = templateHeader.First().Count(); foreach (Row row in dataRows) { //If a column is missing from row, add an empty value IEnumerable <Cell> cells = from cell in row.Descendants <Cell>() select cell; int columnCounter = 0; List <string> dataValues = new List <string>(); foreach (var cell in cells) { string colReference = cell.CellReference; int colIndex = GetColumnIndexFromName(GetColumnName(colReference)); // if column index is higher than what it should be, add blank space to it until it matches up. if (columnCounter < colIndex) { for (int i = columnCounter; i < colIndex; i++) { dataValues.Add(""); } } string cellValue = string.Empty; if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString)) { int id = -1; if (Int32.TryParse(cell.InnerText, out id)) { SharedStringItem item = GetSharedStringItemById(document.WorkbookPart, id); if (item.Text != null) { cellValue = item.Text.Text; } else if (item.InnerText != null) { cellValue = item.InnerText; } else if (item.InnerXml != null) { cellValue = item.InnerXml; } } } else if (cell.CellValue != null) { cellValue = cell.CellValue.InnerText; } dataValues.Add(cellValue); columnCounter = colIndex + 1; } if (dataValues.Count() < numCols) { for (int i = dataValues.Count(); i < numCols; i++) { dataValues.Add(""); } } else if (dataValues.Count() > numCols) { // if dataValues is more than number of column headers, delete the last data for (int i = dataValues.Count(); i > numCols; i--) { dataValues.RemoveAt(i - 1); } } if (arrayNotEmpty(dataValues.ToArray())) { UpdateLookups(enterpriseId, dataValues.ToArray(), formsRepo); } else { break; } } document.Close(); } return(true); } catch (Exception ex) { Console.WriteLine(ex.Message); throw ex; return(false); } }
/// <summary> /// 指定されたパスをソースにコンバート行う /// </summary> /// <param name="xlsxPath"></param> /// <param name="outputDir"></param> static void ImportFromXlsx(string xlsxPath, string outputDir = null) { string PATH = xlsxPath; string OUTPUT_FORMAT = LanguageType.ASSET_NAME_FORMAT + ".asset"; string OUTPUT_DIR = outputDir; if (string.IsNullOrEmpty(outputDir)) { OUTPUT_DIR = EditorUtility.SaveFolderPanel("Choose Output Folder", "Assets/", "Resources"); } // OUTPUT_DIR: To relative path { System.Uri projectUri = new System.Uri(Path.GetFullPath("./")); System.Uri outputUri = new System.Uri(OUTPUT_DIR); OUTPUT_DIR = projectUri.MakeRelativeUri(outputUri).ToString(); Debug.Log(OUTPUT_DIR); } if (string.IsNullOrEmpty(OUTPUT_DIR)) { return; } if (string.IsNullOrEmpty(xlsxPath) || xlsxPath.EndsWith(".xlsx") == false) { EditorUtility.DisplayDialog("Error", Path.GetFileName(xlsxPath) + " is not xlsx file", "OK"); return; } const int COL_ID_POSITION = 0; // ID値格納位置(列)※Excel上での管理用途。インポートでは使わない。 const int COL_SYMBOL_POSITION = 1; // シンボル名格納位置(列) const int COL_LANG_START_POSITION = 2; // 単語格納開始位置(列) const int COL_INDEX_NUM = COL_LANG_START_POSITION - 1; // 単語データが格納開始されるまでの列数 const int ROW_START_DATA_POSITION = 1; // 単語データ格納開始行 List <string> enumSymbols = new List <string>(128); List <string> defaultLang = new List <string>(128); System.Func <Worksheet, Row, int, Cell> ValidateCell = (sheet, row, col) => { Cell cell = SpreadSheetUtil.GetCell(row, col); if (cell == null || cell.IsNullOrEmpty()) { return(null); } return(cell); }; using (SpreadsheetDocument xlsx = SpreadsheetDocument.Open(PATH, false, new OpenSettings { AutoSave = false })) { WorkbookPart workbookpart = xlsx.WorkbookPart; Sheet sheet = workbookpart .Workbook .GetFirstChild <Sheets>() .Elements <Sheet>() .FirstOrDefault(s => s.Name == "List"); WorksheetPart worksheetPart = (WorksheetPart)workbookpart.GetPartById(sheet.Id.Value); Worksheet worksheet = worksheetPart.Worksheet; foreach (var r in worksheet.GetFirstChild <SheetData>().Elements <Row>().Select((Value, Index) => new { Value, Index })) { if (r.Index < ROW_START_DATA_POSITION) { continue; } Cell idCell = ValidateCell(worksheet, r.Value, COL_ID_POSITION); Cell symbolCell = ValidateCell(worksheet, r.Value, COL_SYMBOL_POSITION); if (idCell == null || symbolCell == null) { continue; } enumSymbols.Add(SpreadSheetUtil.GetStringValue(workbookpart, symbolCell)); } for (int langIndex = 0; langIndex < LanguageType.LANG_NUMS; langIndex++) { Language lang = ScriptableObject.CreateInstance <Language>(); bool added = false; foreach (var r in worksheet.GetFirstChild <SheetData>().Elements <Row>().Select((Value, Index) => new { Value, Index })) { if (r.Index < ROW_START_DATA_POSITION) { continue; } Cell idCell = ValidateCell(worksheet, r.Value, COL_ID_POSITION); Cell symbolCell = ValidateCell(worksheet, r.Value, COL_SYMBOL_POSITION); if (idCell == null || symbolCell == null) { continue; } Cell cell = SpreadSheetUtil.GetCell(r.Value, COL_LANG_START_POSITION + langIndex); string word = ""; if (cell != null && !cell.IsNullOrEmpty()) { word = SpreadSheetUtil.GetStringValue(workbookpart, cell); } lang.wordList.Add(word); if (langIndex == (int)LanguageType.Language.Ja) { defaultLang.Add(word); } added = true; } AssetDatabase.CreateAsset(lang, Path.Combine(OUTPUT_DIR, string.Format(OUTPUT_FORMAT, LanguageType.LANG_LIST[langIndex]))); } } GenerateIndexEnum(enumSymbols, defaultLang); AssetDatabase.Refresh(); EditorUtility.DisplayDialog("Import", "Done.", "OK"); }
public void ReadSheet(Stream stream, ReadSheetDicOptions option) { Inspector.NotNull(stream, "Excel文件流不能为空"); Inspector.NotNull(option, $"{nameof(ReadSheetDicOptions)} can not be null"); Inspector.Validation(option.ExcelFields == null || option.ExcelFields.Length == 0 || option.ExcelFields.Count(t => string.IsNullOrWhiteSpace(t.field)) > 0, "Excel中的列头信息不能为空或存在为空的列名"); //匹配SheetName var sheetName = ""; { var sheetNames = this.GetSheetNames(stream, false); Inspector.Validation(option.ReadWay == ReadWay.SheetIndex && option.SheetIndex > sheetNames.Count(), $"指定的SheetIndex {option.SheetIndex} 无效,实际只存在{sheetNames.Count()}个Sheet"); Inspector.Validation(option.ReadWay == ReadWay.SheetName && !sheetNames.Contains(option.SheetName), $"指定的SheetName {option.SheetName} 不存在"); sheetName = option.ReadWay switch { ReadWay.SheetIndex => sheetNames.ElementAt(option.SheetIndex - 1), ReadWay.SheetName => option.SheetName }; } //Excel中的表头列信息(index:集合中元素的位置,cellRef:单元格的A1 B1中的A B这种) var fieldLoc = new List <(int index, string excelField, ColumnType columnType, bool allowNull, string cellRef)>(); { for (int index = 0; index < option.ExcelFields.Count(); index++) { Inspector.Validation(fieldLoc.Exists(t => t.excelField == option.ExcelFields[index].field?.Trim()), "指定读取的 ExcelFields 中存在相同的列名"); fieldLoc.Add((index, option.ExcelFields[index].field, option.ExcelFields[index].type, option.ExcelFields[index].allowNull, "")); } } using (var sheetDoc = SpreadsheetDocument.Open(stream, false)) { WorkbookPart workbookPart = sheetDoc.WorkbookPart; //1.目标Sheet的Rid是否存在 string rId = workbookPart.Workbook.Sheets?.Cast <Sheet>()?.FirstOrDefault(t => t.Name.Value == sheetName)?.Id?.Value; Inspector.NotNullOrWhiteSpace(rId, $"不存在名为 {sheetName} 的Sheet"); SharedStringTablePart shareStringPart; if (workbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0) { shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First(); } else { shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>(); } string[] shareStringItemValues = shareStringPart.GetItemValues().ToArray(); //2.反转Sheet顺序 foreach (var workSheetPart in workbookPart.WorksheetParts?.Reverse()) { //是否是指定Sheet的Rid,不是则忽略 string partRelationshipId = workbookPart.GetIdOfPart(workSheetPart); if (partRelationshipId != rId) { continue; } //读取失败的原始数据信息 (Dictionary <string, object> odata, List <(string rowIndex, string columnName, string cellValue, string errorMsg)> failInfos)failRowData = (new Dictionary <string, object>(), new List <(string rowIndex, string columnName, string cellValue, string errorMsg)>()); //创建Reader OpenXmlReader reader = OpenXmlReader.Create(workSheetPart); //工具类实例 var reflection = ReflectionHelper.NewInstance; while (reader.Read()) { if (reader.ElementType == typeof(Worksheet)) { reader.ReadFirstChild(); } if (reader.ElementType == typeof(Row)) { var row = (Row)reader.LoadCurrentElement(); //3.读取表头列,匹配字段信息 if (row.RowIndex == option.HeadRow) { foreach (Cell cell in row.Elements <Cell>()) { if (cell.CellReference != null && cell.CellReference.HasValue) { //excel中的表头列字段 string excelField = cell.GetValue(shareStringItemValues); if (fieldLoc.Exists(t => t.excelField == excelField)) { var fieldInfo = fieldLoc.FirstOrDefault(t => t.excelField == excelField); fieldInfo.cellRef = StringHelper.RemoveNumber(cell.CellReference); fieldLoc[fieldInfo.index] = fieldInfo; } } } //实体上定义了ExcelKit特性的字段未在Excel中匹配到 var unMatchedField = fieldLoc.Where(t => string.IsNullOrWhiteSpace(t.cellRef)); if (unMatchedField.Count() > 0) { var unmatchFields = string.Join("、", unMatchedField.Select(t => t.excelField)); var msg = $"指定的ExcelFields中的字段【{unmatchFields}】不存在于Excel中"; throw new ExcelKitException(msg); } continue; } if (row.RowIndex < option.DataStartRow) { continue; } if (option.DataEndRow.HasValue && row.RowIndex > option.DataEndRow) { break; } //读取到的每行数据 var rowData = new Dictionary <string, object>(); //excel原始数据 failRowData.odata.Clear(); //失败信息 failRowData.failInfos.Clear(); //是否读取成功 var readSuc = true; //4. row.Elements<Cell>()获取出来的会自动跳过为空的单元格 foreach (Cell cell in row.Elements <Cell>()) { //4.1 跳过cell引用为空的 if (cell.CellReference == null || !cell.CellReference.HasValue) { continue; } //4.2 当前循环的cell列位置(不含数字) var loopCellRef = StringHelper.RemoveNumber(cell.CellReference); //不存在或匹配列信息不一致的跳过 var fieldInfo = fieldLoc.FirstOrDefault(t => t.cellRef.Equals(loopCellRef, StringComparison.OrdinalIgnoreCase)); if (fieldInfo == (0, null, 0, false, null) || !loopCellRef.Equals(fieldInfo.cellRef, StringComparison.OrdinalIgnoreCase)) { continue; } //Excel中读取到的值 string readVal = null; try { readVal = cell.GetValue(shareStringItemValues); Inspector.Validation(!fieldInfo.allowNull && string.IsNullOrWhiteSpace(readVal), $"Excel中列 {fieldInfo.excelField} 为必填项"); object value = ColumnTypeMapping.Convert(readVal, fieldInfo.columnType, fieldInfo.allowNull); rowData.Add(fieldInfo.excelField, value); } catch (Exception ex) { readSuc = false; failRowData.failInfos.Add((row.RowIndex, fieldInfo.excelField, readVal?.ToString(), ex.Message)); } } //5.单元格为空缺失的key补齐(这样做key的顺序和原始的不一致了,有需求时可以使用header上面的cellRef排序解决,为了读取速度此处暂不做) var lackKeys = fieldLoc.Select(t => t.excelField).Except(rowData.Keys); foreach (var lackKey in lackKeys) { rowData.TryAdd(lackKey, null); } //读取成功执行 if (readSuc) { option.SucData?.Invoke(rowData, row.RowIndex.Value); } else { option.FailData?.Invoke(failRowData.odata, failRowData.failInfos); } } } } sheetDoc.Close(); } }
public static DataTable ExtractExcelSheetValues(Stream excelStream, string sheetName) { DataTable dt = new DataTable(); using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(excelStream, true)) { //Access the main Workbook part, which contains data WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart; Sheet ss = ExcelUtility.FindSheet(sheetName, workbookPart); WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id); SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart; if (worksheetPart != null) { Row lastRow = worksheetPart.Worksheet.Descendants <Row>().LastOrDefault(); Row headerRow = worksheetPart.Worksheet.Descendants <Row>().Skip(1).First(); if (headerRow != null) { foreach (Cell c in headerRow.ChildElements) { string value = GetValue(c, stringTablePart); dt.Columns.Add(value); } } if (lastRow != null) { for (int i = 1; i <= lastRow.RowIndex; i++) { DataRow dr = dt.NewRow(); bool empty = true; Row row = worksheetPart.Worksheet.Descendants <Row>().Where(r => i == r.RowIndex).FirstOrDefault(); int j = 0; if (row != null) { foreach (Cell c in row.ChildElements) { //Get cell value string value = GetValue(c, stringTablePart); if (!string.IsNullOrEmpty(value) && value != " ") { empty = false; } var col = ConvertColumnLettering(c.CellReference.Value[0]); if (col >= 0) { dr[col] = value; } j++; if (j == dt.Columns.Count) { break; } } if (empty) { break; } dt.Rows.Add(dr); } } } } } return(dt); }
public void ReadRows(Stream stream, ReadRowsOptions option) { Inspector.NotNull(stream, "Excel文件流不能为空"); Inspector.NotNull(option, $"{nameof(ReadRowsOptions)}不能为null"); if (option.ReadWay == ReadWay.SheetIndex) { Inspector.MoreThanOrEqual(option.SheetIndex, 1, "Sheet索引至少从1开始"); } if (option.ReadWay == ReadWay.SheetName) { Inspector.NotNullOrWhiteSpace(option.SheetName, "Sheet名称不能为空"); } //匹配SheetName var sheetName = ""; { var sheetNames = this.GetSheetNames(stream, false); Inspector.Validation(option.ReadWay == ReadWay.SheetIndex && option.SheetIndex > sheetNames.Count(), $"指定的Sheet索引 {option.SheetIndex} 无效,实际只存在{sheetNames.Count()}个Sheet"); Inspector.Validation(option.ReadWay == ReadWay.SheetName && !sheetNames.Contains(option.SheetName), $"指定的Sheet名称 {option.SheetName} 不存在"); sheetName = option.ReadWay switch { ReadWay.SheetIndex => sheetNames.ElementAt(option.SheetIndex - 1), ReadWay.SheetName => option.SheetName }; } //包含返回空单元格校验 Inspector.Validation(option.ReadEmptyCell && (option.ColumnHeaders == null || option.ColumnHeaders.Count() == 0), $"读取要包含空格列时,必须指定读取的列头信息,{nameof(option.ColumnHeaders)}不能为空"); if (option.ReadEmptyCell) { foreach (var cellRef in option.ColumnHeaders) { Inspector.Validation(!_excelColumn.Contains(cellRef), $"{nameof(option.ColumnHeaders)}中 {cellRef} Excel列头无效"); } } using (var sheetDoc = SpreadsheetDocument.Open(stream, false)) { WorkbookPart workbookPart = sheetDoc.WorkbookPart; SharedStringTablePart shareStringPart; if (workbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0) { shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First(); } else { shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>(); } string[] shareStringItemValues = shareStringPart.GetItemValues().ToArray(); //目标Sheet的Rid string rId = workbookPart.Workbook.Sheets?.Cast <Sheet>()?.FirstOrDefault(t => t.Name.Value == sheetName)?.Id?.Value; Inspector.NotNullOrWhiteSpace(rId, $"不存在名为:{sheetName} 的Sheet"); //读取多个Sheet foreach (var worksheetPart in workbookPart.WorksheetParts?.Reverse()) { //是否是指定Sheet的Rid string partRelationshipId = workbookPart.GetIdOfPart(worksheetPart); if (partRelationshipId != rId) { continue; } OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); //数据行 var list = new List <(string cellRef, string value)>(); while (reader.Read()) { if (reader.ElementType == typeof(Worksheet)) { reader.ReadFirstChild(); } if (reader.ElementType == typeof(Row)) { var row = (Row)reader.LoadCurrentElement(); if (row.RowIndex < option.DataStartRow) { continue; } if (option.DataEndRow.HasValue && row.RowIndex > option.DataEndRow) { break; } list.Clear(); foreach (Cell cell in row.Elements <Cell>()) { if (cell.CellReference == null || !cell.CellReference.HasValue) { continue; } //无数字的列头名(A1、B2、C1中的A、B、C) var loopCellRef = StringHelper.RemoveNumber(cell.CellReference); if (option.ReadEmptyCell && !option.ColumnHeaders.Contains(loopCellRef)) { continue; } string value = cell.GetValue(shareStringItemValues); list.Add((loopCellRef, value)); } //包含空单元格,实际却没有则补齐数据 if (option.ReadEmptyCell) { var willAdd = option.ColumnHeaders.Except(list.Select(t => t.cellRef)); list.AddRange(willAdd.Select(item => (item, ""))); list = list.OrderBy(t => t.cellRef).ToList(); } option.RowData?.Invoke(list.Select(t => t.value).ToList()); } } } sheetDoc.Close(); } if (option.IsDisposeStream) { stream.Dispose(); } }
/// <summary>Reads data from a work sheet.</summary> /// <param name="dataStream">The stream that contains the worksheet</param> /// <param name="worksheetName">The worksheet name/tab that you want to read.</param> /// <param name="hasHeaderRow">Indicates if the first row in the worksheet is a header row. If it does, we will use it /// to determine how things should map to the class; otherwise, we will just assue the class is indexed properly with /// the ExcelMappperAtttribute (Order property) and try to pull the data out that way.</param> /// <param name="startRow">Starting row. This is a ONE based number, but keep in mind that if hasHeaderRow is true, the header row IS row 1. /// If the value is null, it is not used.</param> /// <param name="endRow">Ending row. This is a ONE based number, but keep in mind that if hasHeaderRow is true, the header row IS row 1. /// If the value is null, it is not used.</param> /// <returns>List of objects</returns> public List <T> ReadWorksheet(Stream dataStream, string worksheetName, bool hasHeaderRow = true, int?startRow = null, int?endRow = null) { var result = new List <T>(); if (dataStream == null) { throw new ArgumentException("You must specify data!"); } if (string.IsNullOrEmpty(worksheetName)) { throw new ArgumentException("You must specify a worksheet name!"); } List <ClassToExcelColumn> columns = CreateColumns(worksheetName, typeof(T)); //Open the Excel file using (SpreadsheetDocument doc = SpreadsheetDocument.Open(dataStream, false)) { WorkbookPart workbookPart = doc.WorkbookPart; SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); SharedStringTable sst = sstpart != null ? sstpart.SharedStringTable : null; Sheet theSheet = workbookPart.Workbook.Descendants <Sheet>().FirstOrDefault(s => s.Name == worksheetName); if (theSheet == null) { return(result); } WorksheetPart theWorksheetPart = (WorksheetPart)workbookPart.GetPartById(theSheet.Id); Worksheet theWorksheet = theWorksheetPart.Worksheet; var rows = theWorksheet.Descendants <Row>(); //Loop through the Worksheet rows. int rowIndex = 0; foreach (Row oneRow in rows) { rowIndex++; // Use the first row to add columns to DataTable. if (hasHeaderRow && rowIndex == 1) { RemapExcelColumnLetters(oneRow, columns, sst); } if (startRow.HasValue && startRow.Value > rowIndex) { continue; } if (hasHeaderRow == false || rowIndex > 1) { //Add rows to DataTable. var newItem = new T(); foreach (Cell oneCell in oneRow.Elements <Cell>()) { string columnLetter = GetColumnLetter(oneCell); ClassToExcelColumn column = columns.FirstOrDefault(w => w.ExcelColumnLetter == columnLetter); AssignValue(newItem, column, oneCell, sst, rowIndex); } result.Add(newItem); } if (endRow.HasValue && endRow.Value == rowIndex) { break; } } } return(result); }
public void Insert(string[][] records, int sheetId, string srcFile) { this.GenSheetInfo(srcFile); // Read xlsx original file. using (var doc = SpreadsheetDocument.Open(srcFile, true)) { WorkbookPart workbookPart = doc.WorkbookPart; Worksheet worksheet = null; string rid = this.GetRid(sheetId); foreach (var worksheetPart in workbookPart.WorksheetParts) { if (doc.WorkbookPart.GetIdOfPart(worksheetPart) == rid) { worksheet = worksheetPart.Worksheet; } else { var ws = worksheetPart.Worksheet; var rows = ws.Descendants <Row>(); if (rows != null) { rows.SelectMany(s => s.Elements <Cell>()) .Where(s => s.CellFormula != null) .ToList() .ForEach(s => s.CellFormula.CalculateCell = true); } } } // Inserted data SheetData sheetData = worksheet.GetFirstChild <SheetData>(); //sheetData.RemoveAllChildren(); // Remove all the child nodes from the sheetData node. if (null == records || records.Length == 0) { return; } for (int i = 0; i < records.Length; i++) { if (null == records[i] || records[i].Length == 0) { return; } Row row = new Row(); for (int j = 0; j < records[i].Length; j++) { Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = !string.IsNullOrEmpty(records[i][j]) ? new CellValue(records[i][j]) : new CellValue(string.Empty); row.AppendChild(cell); } sheetData.Append(row); } worksheet.Save(); doc.Save(); } }
public void Main() { string user, password, path; // クラウドサービスのリストを取得する List <string> listCloudService = new List <string>(); int index = -1; string systemPath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location).ToString(); if (!(File.Exists(systemPath + "\\CloudServices_Information\\CloudServices.xlsx"))) { //log.WriteLog("ファイルエクセルクラウドサービス情報が存在しません", null); Console.WriteLine("ファイルエクセルクラウドサービス情報が存在しません"); return; } // Excelファイル情報クラウドサービスへのパス path = systemPath + "\\CloudServices_Information\\CloudServices.xlsx"; SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false); WorkbookPart workbookPart = doc.WorkbookPart; Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById("rId1")).Worksheet; SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>(); var lastRow = thesheetdata.Descendants <Row>().LastOrDefault(); var lastRowToInt = Int32.Parse(lastRow.RowIndex); Cell theCellUser = (Cell)thesheetdata.ElementAt(1).ChildElements.ElementAt(4); SharedStringItem textUser = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellUser.InnerText)); user = textUser.Text.Text; Cell theCellPassword = (Cell)thesheetdata.ElementAt(1).ChildElements.ElementAt(5); SharedStringItem textPassword = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellPassword.InnerText)); password = textPassword.Text.Text; // クラウドサービスのステータスのリストを取得する for (int i = 1; i < lastRowToInt - 1; i++) { Cell theCellCheck = (Cell)thesheetdata.ElementAt(i).ChildElements.ElementAt(1); Int32.TryParse(theCellCheck.InnerText, out index); string textCheck = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(index).InnerText; if ((String.Compare("〇", textCheck)) == 0) { Cell theCellCloudService = (Cell)thesheetdata.ElementAt(i).ChildElements.ElementAt(2); SharedStringItem textCloudService = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellCloudService.InnerText)); listCloudService.Add(textCloudService.Text.Text); } } var stateStoping = "stop"; MyTestCaseTest test_case = new MyTestCaseTest(); test_case.SetUp(); test_case.StartAzure(user, password); for (int i = 0; i < listCloudService.Count; i++) { if (test_case.TestCases(listCloudService[i], stateStoping) == true) { //log.WriteLog(stateStoping + " " + listCloudService[i], "OK"); Console.WriteLine(stateStoping + " " + listCloudService[i] + " " + "OK"); } else { //log.WriteLog(stateStoping + " " + listCloudService[i], "ERROR"); Console.WriteLine(stateStoping + " " + listCloudService[i] + " " + "ERROR"); } } test_case.TearDown(); }
public string[][] Read(string filePath) { string[][] data = null; if (string.IsNullOrEmpty(filePath) || !File.Exists(filePath)) { return(data); } try { using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart; WorksheetPart worksheetPart = null; string rid = this.GetRid(OXExcelManager.SheetIndex); foreach (var sheetPart in workbookPart.WorksheetParts) { if (spreadsheetDoc.WorkbookPart.GetIdOfPart(sheetPart) == rid) { worksheetPart = sheetPart; } } if (null == worksheetPart) { return(data); } SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); if (null == sheetData.Elements <Row>() || sheetData.Elements <Row>().Count() == 0) { return(data); } var rows = sheetData.Elements <Row>().ToList(); if (null == rows || rows.Count == 0) { return(data); } data = new string[rows.Count - 1][]; for (int i = 1; i < rows.Count(); i++) { var cells = rows[i].Elements <Cell>().ToList(); if (null == cells || cells.Count == 0) { return(data); } int cIndex = 0; data[i - 1] = new string[this.fieldCount]; for (int j = 0; j < this.fieldCount; j++) { if (cIndex < cells.Count) { if (cells[cIndex].CellReference == null) { string errMsg = "Bad format!"; throw new OpenXMLCellReferenceNullException(errMsg); } if (this.SeparateColumnName(cells[cIndex].CellReference) == this.columnMarks[j]) { var scNotationPattern = "^((-?\\d+.?\\d*)[Ee]{1}(-?\\d+))$"; var cellVal = GetCellValue(spreadsheetDoc, cells[cIndex++]); if (Regex.IsMatch(cellVal, scNotationPattern)) { cellVal = Convert.ToDecimal(Decimal.Parse(cellVal, System.Globalization.NumberStyles.Float)).ToString(); } data[i - 1][j] = cellVal; } else { data[i - 1][j] = string.Empty; } } else { data[i - 1][j] = string.Empty; } } } } } catch (IOException io_ex) { throw io_ex; } catch (Exception ex) { throw ex; } return(data); }
/// <summary> /// Extract an Excel Spreadsheet based on the given sheet /// </summary> /// <param name="input"></param> /// <param name="output"></param> /// <returns></returns> public override IEnumerable <IRow> Extract(IUnstructuredReader input, IUpdatableRow output) { var stream = input.BaseStream; if (input.Length <= 0) { yield break; } var inputBuffer = new byte[input.Length]; stream.Read(inputBuffer, 0, (int)input.Length); MStream.Write(inputBuffer, (int)MStream.Length, inputBuffer.Length); var document = SpreadsheetDocument.Open(MStream, false); var workbookPart = document.WorkbookPart; WorksheetPart worksheetPart; if (string.IsNullOrEmpty(_sheet)) { worksheetPart = workbookPart.WorksheetParts.First(); } else { var sheet = workbookPart.Workbook.Descendants <Sheet>().FirstOrDefault(s => s.Name == _sheet); worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id); } var sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); var rowCount = 1; string value = null; foreach (var row in sheetData.Elements <Row>()) { foreach (var queryColumn in output.Schema) { var addressName = $"{queryColumn.Name}{rowCount}"; var theCell = row.Descendants <Cell>().FirstOrDefault(c => c.CellReference == addressName); if (theCell != null) { value = theCell.InnerText; if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } output.Set <object>(queryColumn.Name, Convert.ChangeType(value, queryColumn.Type)); } rowCount++; yield return(output.AsReadOnly()); } }
public async Task <DataSet> ReadDocument(string fileName, bool isFirstRowHeader = false) { var fileExtension = Path.GetExtension(fileName); if (!fileExtension.IsSupportedExcelFile()) { throw new FileFormatException("The file format is not supported!"); } var dataSet = new DataSet(); if (fileExtension == SupportFormats.csv) { var table = new DataTable(); using (var stremReader = new StreamReader(fileName)) { var headers = stremReader.ReadLine().Split(','); foreach (string header in headers) { table.Columns.Add(header); } while (!stremReader.EndOfStream) { var rows = stremReader.ReadLine().Split(','); var dataRow = table.NewRow(); for (int i = 0; i < headers.Length; i++) { dataRow[i] = rows[i]; } table.Rows.Add(dataRow); } } dataSet.Tables.Add(table); return(dataSet); } else { try { using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false)) { var workbookPart = doc.WorkbookPart; #region Sheets processing foreach (var sheet in workbookPart.Workbook.Descendants <Sheet>()) { var table = new DataTable(sheet.Name); var dataList = new List <TableRowItem>(); var sheetColumns = new Dictionary <string, string>(); var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id)); var sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); dataSet.Tables.Add(table); if (sheetData.Elements <Row>().Any(x => x.RowIndex == null)) { throw new NotSupportedException("The version of this file is not supported!"); } #region Define header if (isFirstRowHeader) { foreach (var row in sheetData?.Elements <Row>()?.Where(x => x.RowIndex == 1)) { if (row.Elements <Cell>().Any()) { foreach (var cell in row.Elements <Cell>()) { var columnAddress = await GetColumnName(cell.CellReference.Value); var columnName = await GetCellValue(cell, workbookPart); sheetColumns.TryGetValue(columnName, out string colName); if (string.IsNullOrEmpty(colName)) { sheetColumns.TryAdd(columnAddress, columnName); } } } else { throw new MissingFieldException("The column name in the first row was not found!"); } } } else { foreach (var row in sheetData?.Elements <Row>()) { foreach (var cell in row.Elements <Cell>()) { var columnAddress = await GetColumnName(cell.CellReference.Value); sheetColumns.TryGetValue(columnAddress, out string colName); if (string.IsNullOrEmpty(colName)) { sheetColumns.TryAdd(columnAddress, columnAddress); } } } } #endregion #region Sort columns var columnsList = sheetColumns.Values.ToList(); if (!isFirstRowHeader) { columnsList.Sort(); } foreach (var col in columnsList) { table.Columns.Add(col); } #endregion #region Filling up dataSet foreach (var row in sheetData.Elements <Row>()) { var dtsTableRow = table.NewRow(); foreach (var cell in row.Elements <Cell>()) { var columnAddress = await GetColumnName(cell.CellReference.Value); var columnIndex = await GetColumnIndex(cell.CellReference.Value); sheetColumns.TryGetValue(columnAddress, out string columnName); int.TryParse(columnIndex, out int index); var cellValue = await GetCellValue(cell, workbookPart); var firstField = isFirstRowHeader ? 1 : 0; if (index > firstField) { dataList.Add(new TableRowItem { Index = index, Name = columnName, Value = cellValue }); } } } for (var i = isFirstRowHeader ? 2 : 1; i <= dataList.Max(x => x.Index); i++) { var dataRow = table.NewRow(); var tempList = dataList.Where(x => x.Index == i).ToList(); foreach (var item in tempList) { dataRow[item.Name] = item.Value; } table.Rows.Add(dataRow); } #endregion } #endregion } } } catch (Exception e) { throw e; } } return(dataSet); }
/// <summary> /// Converting a XLSX File to a CSV File /// </summary> /// <param name="excelFilePath">Full xlsx file path</param> public static bool Converter_XlsxToCsv(string excelFilePath) { var fileToWrite = System.IO.Path.Combine(Path.GetDirectoryName(excelFilePath), System.IO.Path.GetFileNameWithoutExtension(excelFilePath) + ".csv"); var outputFile = new StreamWriter(fileToWrite, false, Encoding.UTF8); try { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(excelFilePath, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; WorksheetPart worksheetPart = workbookPart.GetPartById("rId1") as WorksheetPart; int dateIndex = -1; int styleIndex = 0; List <int> countDateForm = new List <int>(); foreach (CellFormat format in workbookPart.WorkbookStylesPart.Stylesheet.CellFormats) { //if (format.NumberFormatId == 14 ) //{ // dateIndex = styleIndex; // break; //} if (format.NumberFormatId == 14) { countDateForm.Add(styleIndex); } ++styleIndex; } var sharedDico = new Dictionary <string, string>(); int i = 0; workbookPart.SharedStringTablePart.SharedStringTable.ChildElements.ToList().ForEach(e => sharedDico.Add(i++.ToString(), e.InnerText.Replace(";", ":").Replace("\n", "").Replace("'", " "))); StringBuilder lineBuilder = null; bool isSharedString = false; char previousColumn = default(char); bool mustAddValue = false; string[] Col; int nb_column = 0; int column = 0; bool firstDetec = false; bool isDate = false; bool isCellFormul = false; OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); while (reader.Read()) { if (reader.LocalName == "row") { if (reader.IsStartElement) { lineBuilder = new StringBuilder(); mustAddValue = false; } else { Col = lineBuilder.ToString().Split(';'); column = Col.Count(); if (firstDetec == false) { nb_column = column; firstDetec = true; } if (nb_column == column && firstDetec == true) { outputFile.WriteLine(lineBuilder.ToString()); } else if (nb_column > column && firstDetec == true) { int nbColMissing = nb_column - column; string sep = null; for (int ajoutCol = 0; ajoutCol < nbColMissing; ajoutCol++) { sep += ';'; } outputFile.WriteLine(lineBuilder.ToString() + sep); } else if (nb_column < column && firstDetec == true) { int nbColMissing = column - nb_column; int countLastChar = 0; for (int ajoutCol = lineBuilder.Length - 1; ajoutCol >= 0; ajoutCol--) { if (lineBuilder[ajoutCol] != ';') { break; } countLastChar++; } if (countLastChar == nbColMissing) { outputFile.WriteLine(lineBuilder.ToString().Substring(0, lineBuilder.Length - nbColMissing)); } else { outputFile.WriteLine(lineBuilder.ToString()); } } else { outputFile.WriteLine(lineBuilder.ToString()); } } continue; } if (reader.IsEndElement) { continue; } if (reader.LocalName == "c") { if (mustAddValue) { lineBuilder.Append(";"); } isSharedString = reader.Attributes.Any(a => a.LocalName == "t"); //isDate = reader.Attributes.Any(a => a.LocalName == "s" && a.Value == dateIndex.ToString()); isDate = reader.Attributes.Any(a => a.LocalName == "s" && countDateForm.Contains(Convert.ToInt32(a.Value))); OpenXmlAttribute codeCellule = reader.Attributes.FirstOrDefault(a => a.LocalName == "r"); if (codeCellule != null) { char cellColumn = Regex.Replace(codeCellule.Value, "[0-9]", "").Last(); int indexCol = ALPHABET.IndexOf(cellColumn); int indexPreviousCol = ALPHABET.IndexOf(previousColumn); int diff = indexCol - indexPreviousCol; if (diff > 1) { for (int j = 1; j < diff; j++) { lineBuilder.Append(";"); } } previousColumn = cellColumn; } mustAddValue = true; continue; } if (reader.LocalName == "f") { isCellFormul = true; } if (reader.LocalName == "v") { string value = reader.GetText(); if (value.StartsWith("#")) { lineBuilder.Append(string.Empty); } else if (isSharedString && !isCellFormul) { lineBuilder.Append(sharedDico[value]); } else if (isSharedString && !isDate) { lineBuilder.Append(value); } else if (isDate && !value.Contains('-') && !value.Contains('+') && !value.Contains('.') && !value.Contains(',')) { lineBuilder.Append(DateTime.FromOADate(double.Parse(value)).ToShortDateString()); } else if (value.Contains("E-") || value.Contains("E+")) { lineBuilder.Append(decimal.Parse(value.ToString(), NumberStyles.Any, CultureInfo.InvariantCulture)); } else { if (value.Contains('.') || value.Contains(',')) { lineBuilder.Append(double.Parse(value, NumberStyles.Any, CultureInfo.InvariantCulture)); } else { lineBuilder.Append(value); } } lineBuilder.Append(";"); isCellFormul = false; mustAddValue = false; continue; } } } outputFile.Close(); return(true); } catch (OleDbException) { return(false); } }
public static void Write(string fileName, WorkbookDfn workbook) { try { if (fileName == null) { throw new ArgumentNullException("fileName"); } if (workbook == null) { throw new ArgumentNullException("workbook"); } FileInfo fi = new FileInfo(fileName); if (fi.Exists) { fi.Delete(); } // create the blank workbook char[] base64CharArray = _EmptyXlsx .Where(c => c != '\r' && c != '\n').ToArray(); byte[] byteArray = System.Convert.FromBase64CharArray(base64CharArray, 0, base64CharArray.Length); File.WriteAllBytes(fi.FullName, byteArray); // open the workbook, and create the TableProperties sheet, populate it using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(fi.FullName, true)) { WorkbookPart workbookPart = sDoc.WorkbookPart; XDocument wXDoc = workbookPart.GetXDocument(); XElement sheetElement = wXDoc .Root .Elements(S.sheets) .Elements(S.sheet) .Where(s => (string)s.Attribute(SSNoNamespace.name) == "Sheet1") .FirstOrDefault(); if (sheetElement == null) { throw new SpreadsheetWriterInternalException(); } string id = (string)sheetElement.Attribute(R.id); sheetElement.Remove(); workbookPart.PutXDocument(); WorksheetPart sPart = (WorksheetPart)workbookPart.GetPartById(id); workbookPart.DeletePart(sPart); XDocument appXDoc = sDoc .ExtendedFilePropertiesPart .GetXDocument(); XElement vector = appXDoc .Root .Elements(EP.TitlesOfParts) .Elements(VT.vector) .FirstOrDefault(); if (vector != null) { vector.SetAttributeValue(SSNoNamespace.size, 0); XElement lpstr = vector.Element(VT.lpstr); lpstr.Remove(); } XElement vector2 = appXDoc .Root .Elements(EP.HeadingPairs) .Elements(VT.vector) .FirstOrDefault(); XElement variant = vector2 .Descendants(VT.i4) .FirstOrDefault(); if (variant != null) { variant.Value = "1"; } sDoc.ExtendedFilePropertiesPart.PutXDocument(); if (workbook.Worksheets != null) { foreach (var worksheet in workbook.Worksheets) { AddWorksheet(sDoc, worksheet); } } workbookPart.WorkbookStylesPart.PutXDocument(); } } catch (Exception e) { Console.WriteLine("Unhandled exception: {0} in {1}", e.ToString(), e.Source); throw e; } }
// Retrieve the value of a cell, given a file name, sheet name, // and address name. public static string GetCellValue(string fileName, string sheetName, string addressName) { string value = null; // Open the spreadsheet document for read-only access. using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false)) { // Retrieve a reference to the workbook part. WorkbookPart wbPart = document.WorkbookPart; // Find the sheet with the supplied name, and then use that // Sheet object to retrieve a reference to the first worksheet. Sheet theSheet = wbPart.Workbook.Descendants <Sheet>(). Where(s => s.Name == sheetName).FirstOrDefault(); // Throw an exception if there is no sheet. if (theSheet == null) { throw new ArgumentException("sheetName"); } // Retrieve a reference to the worksheet part. WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); // Use its Worksheet property to get a reference to the cell // whose address matches the address you supplied. Cell theCell = wsPart.Worksheet.Descendants <Cell>(). Where(c => c.CellReference == addressName).FirstOrDefault(); // If the cell does not exist, return an empty string. if (theCell != null) { value = theCell.InnerText; // If the cell represents an integer number, you are done. // For dates, this code returns the serialized value that // represents the date. The code handles strings and // Booleans individually. For shared strings, the code // looks up the corresponding value in the shared string // table. For Booleans, the code converts the value into // the words TRUE or FALSE. if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: // For shared strings, look up the value in the // shared strings table. var stringTable = wbPart.GetPartsOfType <SharedStringTablePart>() .FirstOrDefault(); // If the shared string table is missing, something // is wrong. Return the index that is in // the cell. Otherwise, look up the correct text in // the table. if (stringTable != null) { value = stringTable.SharedStringTable .ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } } return(value); }
/// <summary> /// Given a document name, a property name/value, and the property type add a custom property to a document. /// </summary> /// <param name="fileName"></param> /// <param name="propertyName"></param> /// <param name="propertyValue"></param> /// <param name="propertyType"></param> /// <param name="fileType"></param> /// <returns>returns the original value, if it existed</returns> public static string SetCustomProperty(string fileName, string propertyName, object propertyValue, PropertyTypes propertyType, string fileType) { string returnValue = null; var newProp = new CustomDocumentProperty(); bool propSet = false; // Calculate the correct type. switch (propertyType) { case PropertyTypes.DateTime: // Be sure you were passed a real date and if so, format in the correct way. // The date/time value passed in should represent a UTC date/time. if ((propertyValue) is DateTime) { newProp.VTFileTime = new VTFileTime(string.Format("{0:s}Z", Convert.ToDateTime(propertyValue))); propSet = true; } break; case PropertyTypes.NumberInteger: if ((propertyValue) is int) { newProp.VTInt32 = new VTInt32(propertyValue.ToString()); propSet = true; } break; case PropertyTypes.NumberDouble: if (propertyValue is double) { newProp.VTFloat = new VTFloat(propertyValue.ToString()); propSet = true; } break; case PropertyTypes.Text: newProp.VTLPWSTR = new VTLPWSTR(propertyValue.ToString()); propSet = true; break; case PropertyTypes.YesNo: if (propertyValue is bool) { // Must be lowercase. newProp.VTBool = new VTBool(Convert.ToBoolean(propertyValue).ToString().ToLower()); propSet = true; } break; } if (!propSet) { // If the code was not able to convert the property to a valid value, throw an exception. MessageBox.Show("The value entered does not match the specific type. The value will be stored as text.", "Invalid Type", MessageBoxButtons.OK, MessageBoxIcon.Warning); newProp.VTLPWSTR = new VTLPWSTR(propertyValue.ToString()); propSet = true; } // Now that you have handled the parameters, start working on the document. newProp.FormatId = "{D5CDD505-2E9C-101B-9397-08002B2CF9AE}"; newProp.Name = propertyName; if (fileType == "Word") { using (var document = WordprocessingDocument.Open(fileName, true)) { var customProps = document.CustomFilePropertiesPart; if (customProps == null) { // No custom properties? Add the part, and the // collection of properties now. customProps = document.AddCustomFilePropertiesPart(); customProps.Properties = new DocumentFormat.OpenXml.CustomProperties.Properties(); } var props = customProps.Properties; if (props != null) { // This will trigger an exception if the property's Name // property is null, but if that happens, the property is damaged, // and probably should raise an exception. var prop = props.Where(p => ((CustomDocumentProperty)p).Name.Value == propertyName).FirstOrDefault(); // Does the property exist? If so, get the return value, // and then delete the property. if (prop != null) { returnValue = prop.InnerText; prop.Remove(); } // Append the new property, and // fix up all the property ID values. // The PropertyId value must start at 2. props.AppendChild(newProp); int pid = 2; foreach (CustomDocumentProperty item in props) { item.PropertyId = pid++; } props.Save(); } } } else if (fileType == "Excel") { using (var document = SpreadsheetDocument.Open(fileName, true)) { var customProps = document.CustomFilePropertiesPart; if (customProps == null) { customProps = document.AddCustomFilePropertiesPart(); customProps.Properties = new DocumentFormat.OpenXml.CustomProperties.Properties(); } var props = customProps.Properties; if (props != null) { var prop = props.Where(p => ((CustomDocumentProperty)p).Name.Value == propertyName).FirstOrDefault(); if (prop != null) { returnValue = prop.InnerText; prop.Remove(); } props.AppendChild(newProp); int pid = 2; foreach (CustomDocumentProperty item in props) { item.PropertyId = pid++; } props.Save(); } } } else { using (var document = PresentationDocument.Open(fileName, true)) { var customProps = document.CustomFilePropertiesPart; if (customProps == null) { customProps = document.AddCustomFilePropertiesPart(); customProps.Properties = new DocumentFormat.OpenXml.CustomProperties.Properties(); } var props = customProps.Properties; if (props != null) { var prop = props.Where(p => ((CustomDocumentProperty)p).Name.Value == propertyName).FirstOrDefault(); if (prop != null) { returnValue = prop.InnerText; prop.Remove(); } props.AppendChild(newProp); int pid = 2; foreach (CustomDocumentProperty item in props) { item.PropertyId = pid++; } props.Save(); } } } return(returnValue); }
public static void ReadFile() { using (SpreadsheetDocument doc = SpreadsheetDocument.Open("File1.xlsx", false)) { //create the object for workbook part WorkbookPart workbookPart = doc.WorkbookPart; Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>(); StringBuilder excelResult = new StringBuilder(); //using for each loop to get the sheet from the sheetcollection foreach (Sheet thesheet in thesheetcollection) { excelResult.AppendLine("Excel Sheet Name : " + thesheet.Name); excelResult.AppendLine("----------------------------------------------- "); //statement to get the worksheet object by using the sheet id Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet; SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild <SheetData>(); foreach (Row thecurrentrow in thesheetdata) { foreach (Cell thecurrentcell in thecurrentrow) { //statement to take the integer value string currentcellvalue = string.Empty; if (thecurrentcell.DataType != null) { if (thecurrentcell.DataType == CellValues.SharedString) { int id; if (Int32.TryParse(thecurrentcell.InnerText, out id)) { SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id); if (item.Text != null) { //code to take the string value excelResult.Append(item.Text.Text + " "); } else if (item.InnerText != null) { currentcellvalue = item.InnerText; } else if (item.InnerXml != null) { currentcellvalue = item.InnerXml; } } } } else { excelResult.Append(Convert.ToInt16(thecurrentcell.InnerText) + " "); } } excelResult.AppendLine(); } excelResult.Append(""); Console.WriteLine(excelResult.ToString()); Console.ReadLine(); } } }