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();
}
}
}
