public static void ReplicateRow(SheetData sheetData, int refRowIndex, int count) { IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value > refRowIndex); foreach (Row row in rows) IncrementIndexes(row, count); Row refRow = GetRow(sheetData, refRowIndex); for (int i = 0; i < count; i++) { Row newRow = (Row)refRow.Clone(); IncrementIndexes(newRow, i + 1); sheetData.InsertAfter(newRow, GetRow(sheetData, refRowIndex + i)); } }
public static void ReplicateRow(SheetData sheetData, int refRowIndex, int count) { Repository.Utility.WriteLog("ReplicateRow started", System.Diagnostics.EventLogEntryType.Information); IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value > refRowIndex); foreach (Row row in rows) IncrementIndexes(row, count); Row refRow = GetRow(sheetData, refRowIndex); for (int i = 0; i < count; i++) { Row newRow = (Row)refRow.Clone(); IncrementIndexes(newRow, i + 1); //sheetData.InsertAt(newRow, i + 1); sheetData.InsertAfter(newRow, GetRow(sheetData, refRowIndex + i)); } Repository.Utility.WriteLog("ReplicateRow completed successfully", System.Diagnostics.EventLogEntryType.Information); }
public LinkedListBasedSheetDataIndexer(SheetData sheetData) { if (sheetData == null) { throw new ArgumentNullException("sheetData"); } long rowIndex = 0; // Order this sucker first so we don't have to sort a Linked List. foreach (var row in sheetData.Descendants<Row>().OrderBy(x => (uint)x.RowIndex)) { var rowIndexer = new RowIndexer(row); rowIndex = rowIndexer.RowIndex; this.rows.AddLast(rowIndexer); } this.maxRowIndex = rowIndex; this.SheetData = sheetData; }
private IEnumerable <Row> GetRows(int firstRowIndex, int?lastRowIndex) { if (_sheetId == null) { return(Enumerable.Empty <Row>( )); } // Note: we are using the worksheet name as a unique ID, because Excel ensures it is unique within the document anyway. string nameToFind = _sheetId; Worksheet workSheet = ExcelHelpers.GetWorksheetByName(_document, nameToFind); SheetData sheetData = workSheet?.GetFirstChild <SheetData>( ); IEnumerable <Row> rows = sheetData?.Descendants <Row>( ) ?.Where(r => r.RowIndex?.Value >= firstRowIndex) .TakeWhile(r => lastRowIndex == null || r.RowIndex?.Value <= lastRowIndex) .Where(r => !ExcelHelpers.IsRowEmpty(_document, r)); if (rows == null) { return(Enumerable.Empty <Row>( )); } return(rows); }
private static DataTable GetExcelDatatoDataTable() { var table = new DataTable(); using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"F:\Study Materials\Projects\Practice\Report.xlsx", 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>(); foreach (Cell cell in rows.ElementAt(0)) { table.Columns.Add(GetCellValue(spreadSheetDocument, cell)); } foreach (Row row in rows) //this will also include your header row... { DataRow tempRow = table.NewRow(); for (int i = 0; i < row.Descendants <Cell>().Count(); i++) { tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants <Cell>().ElementAt(i)); } table.Rows.Add(tempRow); } } table.Rows.RemoveAt(0); return(table); }
public static void CheckCellValue(string filePath, string sheetName, string cellReference, bool isNumeric, string cellValue) { using (var document = SpreadsheetDocument.Open(filePath, true)) { WorkbookPart workbookPart = document.WorkbookPart; SharedStringTable sharedStringTable = workbookPart.SharedStringTablePart.SharedStringTable; var sheet = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetName).First(); WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); var cell = sheetData.Descendants <Cell>().Where(x => x.CellReference.Value == cellReference).FirstOrDefault(); Assert.IsNotNull(cell); if (isNumeric) { Assert.AreEqual(cellValue, cell.CellValue.InnerText); } else { Assert.AreEqual(cellValue, GetSharedStringItem(sharedStringTable, int.Parse(cell.CellValue.InnerText))); } } }
/// <summary> /// Returns string list containing first row from excel sheet. /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static List <string> GetExcelColumnNames(string filePath) { DataTable dt = new DataTable(); using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath, false)) { 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>(); foreach (var openXmlElement in rows.ElementAt(0)) { var cell = (Cell)openXmlElement; dt.Columns.Add(GetCellValue(spreadSheetDocument, cell)); } return(dt.Columns.Cast <DataColumn>() .Select(x => x.ColumnName) .ToList()); } }
/// <summary> /// Adds all the specified values as new rows /// </summary> /// <param name="sheet">SheetData reference</param> /// <param name="data">Object values</param> /// <returns>Task State</returns> public static async Task AddRows(this SheetData sheet, IEnumerable <IEnumerable <object> > data) { //Check whether the sheet reference or the sheet data is a non-valid to work with object value if (sheet.IsNotValid() || data == null || !data.Any()) { return; } //stablish wich is going to be the start index for data to set uint startIndex = sheet .Descendants <Row>() .LastOrDefault()?.RowIndex.Value ?? 0; startIndex++; //Add all the entries for (uint r = 0; r < data.Count(); r++) { //Create a new row Row row = new Row() { RowIndex = startIndex + r }; IEnumerable <object> d = data.ElementAt((int)r); //Add all the entities as a row for (uint c = 0; c < d.Count(); c++) { //Get the entity in turn object e = d.ElementAt((int)c); Cell cell = new Cell(); await cell.SetValue(e); row.AppendChild(cell); } //Add the row to the data sheet sheet.Append(row); } }
public List <ExcelSheet> Parse(ExcelUpload uploadModel) { List <ExcelSheet> returnSheets = new List <ExcelSheet>(); using (MemoryStream ms = new MemoryStream(uploadModel.ExcelFile)) { SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(ms, false); WorkbookPart wbPart = spreadsheet.WorkbookPart; var sheets = wbPart.Workbook.Descendants <Sheet>(); foreach (Sheet sheet in sheets) { ExcelSheet newReturnSheet = new ExcelSheet(); WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id)); Worksheet worksheet = wsPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); newReturnSheet.SheetName = sheet.Name; var firstRowCells = worksheet.Descendants <Cell>().Where(y => GetCellRow(y.CellReference.Value) == 1); foreach (var firstRowCell in firstRowCells) { ExcelColumn newReturnColumn = new ExcelColumn() { Letter = GetCellLetter(firstRowCell.CellReference.Value), Name = GetCellLetter(firstRowCell.CellReference.Value) }; if (uploadModel.FirstRowIsHeader) { newReturnColumn.Name = GetCellValue(firstRowCell, wbPart); } newReturnSheet.Columns.Add(newReturnColumn); } var rows = sheetData.Descendants <Row>(); foreach (var row in rows) { ExcelRow newReturnRow = new ExcelRow() { RowNumber = (int)row.RowIndex.Value }; if (uploadModel.FirstRowIsHeader && newReturnRow.RowNumber == 1) { continue; } foreach (ExcelColumn column in newReturnSheet.Columns) { var cellReference = string.Format("{0}{1}", column.Letter, newReturnRow.RowNumber); Cell cell = worksheet.Descendants <Cell>().SingleOrDefault(y => y.CellReference.Value == cellReference); ExcelCell newReturnCell = new ExcelCell() { Letter = column.Letter, Value = GetCellValue(cell, wbPart) }; newReturnRow.Cells.Add(newReturnCell); } newReturnSheet.Rows.Add(newReturnRow); } returnSheets.Add(newReturnSheet); } } return(returnSheets); }
internal void importByXLSX(string v) { DataTable dt = new DataTable(); List <Dictionary <String, String> > parsedExcelBanca = new List <Dictionary <string, string> >(); using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"C:\Temp\movimenti.xlsx", 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>(); foreach (Cell cell in rows.ElementAt(0)) { dt.Columns.Add(GetCellValue(spreadSheetDocument, cell)); } foreach (Row row in rows) //this will also include your header row... { DataRow tempRow = dt.NewRow(); for (int i = 0; i < row.Descendants <Cell>().Count(); i++) { tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants <Cell>().ElementAt(i)); } dt.Rows.Add(tempRow); } } dt.Rows.RemoveAt(0); //...so i'm taking it out here. foreach (DataRow r in dt.Rows) { //Data valuta;Descrizione operazione;Causale;Importo Dictionary <String, String> excelLine = new Dictionary <string, string>(); String dd = r["Data"].ToString(); String[] splitDate = dd.Split(Char.Parse("/")); DateTime dtdate = new DateTime(int.Parse(splitDate[2]), int.Parse(splitDate[1]), int.Parse(splitDate[0])); String importo = (Decimal.Parse(r[@"Importo (€)"].ToString()) * -1).ToString(); excelLine.Add("data valuta", dtdate.ToString("yyyyMMdd")); excelLine.Add("descrizione operazione", r["descrizione"].ToString()); excelLine.Add("causale", "NEXI IMPORT"); excelLine.Add("importo", importo); parsedExcelBanca.Add(excelLine); } using (FinanceDB dbctx = new FinanceDB()) { dbctx.Configuration.ValidateOnSaveEnabled = false; DateTime baseline = DateTime.ParseExact( dbctx.AppConfiguration.Where(x => x._namespace == "FinancialNoteImport" && x.name == "baselineNexi").SingleOrDefault().value, "yyyy-MM-dd", CultureInfo.InvariantCulture); foreach (var l in parsedExcelBanca) { writeEntryToDB(l, baseline); } } //StringBuilder strBld = new StringBuilder(); //using (FinanceDB dbctx = new FinanceDB()) //{ // dbctx.Configuration.ValidateOnSaveEnabled = false; // dbctx.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); // DateTime baseline = DateTime.ParseExact( // dbctx.AppConfiguration.Where(x => x._namespace == "FinancialNoteImport" && x.name == "baselineNexi").SingleOrDefault().value, // "yyyy-MM-dd", CultureInfo.InvariantCulture); // if (dt.Rows.Count > 0) // { // strBld.AppendLine("INSERT [dbo].[Raw_Finance]([amount], [valueDate], [description], [notes], [appliedTaxes], [appliedWelfare], [appliedSavings], [appliedAi], [appliedFree], [feeType], [n_sequence], [loadDate])"); // strBld.AppendLine("VALUES"); // } // foreach (System.Data.DataRow r in dt.Rows) // { // string values = "(@amount, @valueDate, @description, @notes, NULL, NULL, NULL, NULL, NULL, NULL, NULL, GETDATE()),"; // values = values.Replace("@amount", "'" + (Decimal.Parse(r[@"Importo (€)"].ToString()) * -1).ToString() + "'"); // String dd = r["Data"].ToString(); // String[] splitDate = dd.Split(Char.Parse("/")); // DateTime dtdate = new DateTime(int.Parse(splitDate[2]), int.Parse(splitDate[1]), int.Parse(splitDate[0])); // values = values.Replace("@valueDate", "'" + dtdate.ToString("yyyyMMdd") + "'"); // values = values.Replace("@description", "'" + r["Descrizione"].ToString() + "'"); // values = values.Replace("@notes", "'NEXI IMPORT'"); // if (dtdate >= baseline) // strBld.AppendLine(values); // } //} //using (SqlConnection sconn = new SqlConnection(sqlStrBld.ToString())) //{ // sconn.Open(); // using (var cmd = sconn.CreateCommand()) // { // cmd.CommandType = System.Data.CommandType.Text; // cmd.CommandText = strBld.ToString().Trim().TrimEnd(Char.Parse(",")); // int affected = cmd.ExecuteNonQuery(); // Console.WriteLine("Imported {0} records from stage table.", affected); // } //} }
static void Main(string[] args) { DataTable dt = new DataTable(); using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(Settings.Default.FilePath, 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>(); foreach (Cell cell in rows.ElementAt(0)) { dt.Columns.Add(GetCellValue(spreadSheetDocument, cell)); } 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 { tempRow[columnIndex] = ""; //Insert blank data here; columnIndex++; }while (columnIndex < cellColumnIndex); } tempRow[columnIndex] = GetCellValue(spreadSheetDocument, cell); columnIndex++; } dt.Rows.Add(tempRow); } } dt.Rows.RemoveAt(0); Console.ForegroundColor = ConsoleColor.Green; Console.WriteLine("Enter your user name (ex: [email protected]):"); string _targetUserName = Console.ReadLine(); Console.ForegroundColor = ConsoleColor.Green; Console.WriteLine("Enter your password:"); SecureString _targetPassword = GetPasswordFromConsole(); ClientContext ctx = new ClientContext(Settings.Default.SiteURL); ctx.Credentials = new SharePointOnlineCredentials(_targetUserName, _targetPassword); ctx.ExecuteQuery(); List targetList = ctx.Web.Lists.GetByTitle(Settings.Default.ListName); ctx.Load(targetList); ctx.ExecuteQuery(); foreach (DataRow dr in dt.Rows) { addNewRecord(dr, targetList, ctx); } }
public static Row CreateRow(this Worksheet worksheet, Row refRow) { SheetData sheetData = worksheet.GetFirstChild <SheetData>(); uint newRowIndex = 0; var newRow = new Row() { RowIndex = refRow.RowIndex.Value }; var cells = refRow.Elements <Cell>(); newRow.Height = new DoubleValue(refRow.Height); newRow.CustomHeight = new BooleanValue(refRow.CustomHeight); foreach (Cell cell in cells) { Cell newCell = (Cell)cell.CloneNode(true); newCell.StyleIndex = new UInt32Value(cell.StyleIndex); newRow.Append(newCell); } IEnumerable <Row> rows = sheetData.Descendants <Row>().Where(r => r.RowIndex.Value >= refRow.RowIndex.Value); foreach (Row row in rows) { newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1); foreach (var cell in row.Elements <Cell>()) { // Update the references for reserved cells. string cellReference = cell.CellReference.Value; cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString())); } row.RowIndex = new UInt32Value(newRowIndex); } sheetData.InsertBefore(newRow, refRow); // process merge cell in cloned rows var mcells = worksheet.GetFirstChild <MergeCells>(); if (mcells != null) { //处理所有动态行以下的merg var clonedMergeCells = mcells.Elements <MergeCell>(). Where(m => Utility.GetRowIndex(m.Reference.Value.Split(':')[0]) >= newRow.RowIndex.Value).ToList <MergeCell>(); foreach (var cmCell in clonedMergeCells) { cmCell.Reference.Value = Utility.AddRow(cmCell.Reference.Value); } //增加新的merg var newMergeCells = new List <MergeCell>(); var rowMergeCells = mcells.Elements <MergeCell>(). Where(m => Utility.GetRowIndex(m.Reference.Value.Split(':')[0]) == refRow.RowIndex).ToList <MergeCell>(); foreach (var mc in rowMergeCells) { newMergeCells.Add(new MergeCell() { Reference = mc.Reference.Value.Replace(refRow.RowIndex.Value.ToString(), (newRow.RowIndex.Value).ToString()) }); } uint count = mcells.Count.Value; mcells.Count = new UInt32Value(count + (uint)newMergeCells.Count); mcells.Append(newMergeCells.ToArray()); } return(newRow); }
public static DataSet GetDataSet(string path, string fileName, ref string errorMessage, ref string sessionKey) { ICacheManager cacheManager = new MemoryCacheManager(); DataSet ds = new DataSet(); PagingDataTable dt = null; try { string inputFileName = System.IO.Path.Combine(path, fileName); using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(inputFileName, false)) { //Access the main Workbook part, which contains data WorkbookPart workbookPart = myWorkbook.WorkbookPart; WorksheetPart worksheetPart = null; List <Sheet> sheets = workbookPart.Workbook.Descendants <Sheet>().ToList(); foreach (var ss in sheets) { dt = new PagingDataTable(); dt.TableName = ss.Name; worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id); SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart; if (worksheetPart != null) { string relationshipId = sheets.First().Id.Value; Worksheet workSheet = worksheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); if (rows.ToArray().Count() > 0) { foreach (Cell cell in rows.ElementAt(0)) { dt.Columns.Add(GetCellValue(myWorkbook, cell)); } int rowIndex = 0; foreach (Row row in rows) //this will also include your header row... { if (rowIndex > 0) { 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 { try { tempRow[columnIndex] = ""; //Insert blank data here; } catch { } columnIndex++; }while (columnIndex < cellColumnIndex); } try { tempRow[columnIndex] = GetCellValue(myWorkbook, cell); } catch { } columnIndex++; } bool isAllColumnBlank = true; foreach (DataColumn col in dt.Columns) { if (string.IsNullOrEmpty(Convert.ToString(tempRow[col.ColumnName])) == false) { isAllColumnBlank = false; break; } } if (isAllColumnBlank == false) { dt.Rows.Add(tempRow); } } rowIndex++; } dt.Columns.Add(new DataColumn { DataType = typeof(int), //AutoIncrement = true, //AutoIncrementSeed = 1, //AutoIncrementStep = 1, ColumnName = "RowNumber", //AllowDBNull = false, }); dt.Columns.Add(new DataColumn { ColumnName = "ImportError", }); rowIndex = 1; foreach (DataRow row in dt.Rows) { row["RowNumber"] = rowIndex; rowIndex++; } ds.Tables.Add(dt); } } } } Guid guid = System.Guid.NewGuid(); sessionKey = string.Format(EXCELDATABASE_BY_KEY, guid); cacheManager.Set(sessionKey, ds, 120); } catch (Exception ex) { errorMessage = ex.Message.ToString(); } finally { UploadFileHelper.DeleteFile("TempPath", fileName); } return(ds); }
/// <summary> /// Read in a new translation locale /// </summary> /// <returns>The translation</returns> /// <remarks>This actually loads all data on a single pass for all translations</remarks> /// <param name="stream">The file stream of the code</param> public Dictionary <string, string> Read(Stream stream) { // Get language code from provided stub string code = string.Empty; using (var streamReader = new StreamReader(stream)) { code = streamReader.ReadToEnd().Trim(); } // If theres no code we have to bail, this shouldnt happen, but ya. if (code == string.Empty) { throw new InvalidDataException("No Locale Found In Stub"); } // Check if we have it from the last parse if (LocalizationCache.Locales.ContainsKey(code) && LocalizationCache.Locales[code].Count > 0) { return(LocalizationCache.Locales[code]); } // --- From this point we effectively only do this once -- // Read in our spreadsheet, caching it just in case after the first time through if (_spreadsheet == null) { _spreadsheet = SpreadsheetDocument.Open(GetType().Assembly.GetManifestResourceStream("Galileo.Localization.Locales.strings.xlsx"), false); WorkbookPart workbookPart = _spreadsheet.WorkbookPart; IEnumerable <Sheet> sheets = _spreadsheet.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)_spreadsheet.WorkbookPart.GetPartById(relationshipId); Worksheet workSheet = worksheetPart.Worksheet; _sheetData = workSheet.GetFirstChild <SheetData>(); } // Get all rows (this seems stupid, but we use it later) IEnumerable <Row> rows = _sheetData.Descendants <Row>(); // Create our column index the first time through if (_columnLookup == null) { _columnLookup = new Dictionary <string, string>(); int columnIndex = 1; foreach (Cell cell in rows.ElementAt(0)) { string columnHeader = GetCellValue(cell); if (!columnHeader.StartsWith(CommentPrefix, StringComparison.Ordinal)) { _columnLookup.Add( GetColumnIdentifier(cell), columnHeader); } columnIndex++; } } // Create Key Locale if (!LocalizationCache.Locales.ContainsKey(TranslationLocale)) { LocalizationCache.Locales.Add(TranslationLocale, new Dictionary <string, string>()); } // Building time foreach (Row row in rows) { // Find out key string key = GetCellValue(row.Descendants <Cell>().First()); // Allow comments in rows if (key.StartsWith(CommentPrefix, StringComparison.Ordinal)) { continue; } // Add to base key debug language if (!LocalizationCache.Locales[TranslationLocale].ContainsKey(key)) { LocalizationCache.Locales[TranslationLocale].Add(key, key); } for (int i = 0; i < row.Descendants <Cell>().Count(); i++) { Cell workingCell = row.Descendants <Cell>().ElementAt(i); string columnID = GetColumnIdentifier(workingCell); string cellValue = GetCellValue(workingCell); if (string.IsNullOrEmpty(cellValue)) { continue; } // Is an item if (_columnLookup.ContainsKey(columnID)) { // Check our cache has the right key if (!LocalizationCache.Locales.ContainsKey(_columnLookup[columnID])) { LocalizationCache.Locales.Add(_columnLookup[columnID], new Dictionary <string, string>()); } if (LocalizationCache.Locales[_columnLookup[columnID]].ContainsKey(key)) { LocalizationCache.Locales[_columnLookup[columnID]][key] = cellValue; } else { LocalizationCache.Locales[_columnLookup[columnID]].Add(key, cellValue); } } } } return(LocalizationCache.Locales[code]); }
public void UpdateInvoiceNumberInSheet() { var firstInvoice = orderReports[0]; var invoiceNumber = string.Format("{0}-{1}", firstInvoice.Orders[0].OrderDate.ToString("MMyy"), firstInvoice.Orders[0].SchoolCode); var cellInvoiceNumber = sheetData.Descendants <Cell>().First(c => c.CellReference == "J2"); cellInvoiceNumber.InlineString = new InlineString { Text = new Text { Text = invoiceNumber } }; cellInvoiceNumber.DataType = CellValues.InlineString; }
public static DataTable ExcelWorksheetToDataTable(string pathFilename, string worksheetName, string scriptName) { 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 = 2; 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, 2]; 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) { if (row.Descendants <Cell>().Count() != 0) { Cell cell1 = row.Descendants <Cell>().ElementAt(0); if (cell1.CellValue == null || cell1.CellReference == null) { continue; } String scriptActual = ""; if (cell1.DataType != null && cell1.DataType.Value == CellValues.SharedString) { scriptActual = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(cell1.CellValue.InnerXml)].InnerText; } if (scriptActual.Equals("$Start") || scriptActual.Equals(scriptName)) { 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) - 1; // Needs to be 0-based // 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; } rowInx++; } // 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 < 2; row++) { DataRow dataRow = dt.NewRow(); for (int col = 0; col < numOfColumns; col++) { 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 < 2; rowInx++) { for (colInx = 0; colInx < numOfColumns; 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 List <ApproverExcelModel> GetDataTableFromSpreadsheet(Stream MyExcelStream, bool ReadOnly, HRProfile hRProfile) { List <ApproverExcelModel> dt = new List <ApproverExcelModel>(); using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(MyExcelStream, ReadOnly)) { WorkbookPart workbookPart = sDoc.WorkbookPart; IEnumerable <Sheet> sheets = sDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)sDoc.WorkbookPart.GetPartById(relationshipId); Worksheet workSheet = worksheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); foreach (Cell cell in rows.ElementAt(0)) { //dt.Add(GetCellValue(sDoc, cell)); } ApproverExcelModel approverExcelModel; Debug.WriteLine("rows length = " + rows.Count()); foreach (Row row in rows) { approverExcelModel = new ApproverExcelModel(); //approverExcelModel.entrykey = ( int )row.RowIndex.Value; for (int i = 0; i < row.Descendants <Cell>().Count(); i++) { Debug.WriteLine("i = " + i); //approverExcelModel = new ApproverExcelModel(); switch (i) { case 0: //approvername approverExcelModel.approvername = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 1: //approverid approverExcelModel.approverid = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 2: //role approverExcelModel.role = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 3: //roleid approverExcelModel.roleid = int.Parse(GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i))); break; case 4: //unitcode approverExcelModel.unitcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 5: //unitname approverExcelModel.unitname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 6: //deptcode approverExcelModel.deptcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 7: //deptname approverExcelModel.deptname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 8: //groupcode approverExcelModel.groupcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 9: //groupname approverExcelModel.groupname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 10: //supergroupcode approverExcelModel.supergroupcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 11: //supergroupname approverExcelModel.supergroupname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 12: //comments approverExcelModel.comments = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); //approverExcelModel.HRProfile = hRProfile; approverExcelModel.hrstaffname = hRProfile.name; approverExcelModel.hrstaffnumber = hRProfile.employee_number; approverExcelModel.entrykey = approverExcelModel.approverid + "_" + approverExcelModel.roleid.ToString() + "_" + approverExcelModel.unitcode + approverExcelModel.deptcode + "_" + approverExcelModel.groupcode + "_" + approverExcelModel.supergroupcode; dt.Add(approverExcelModel); break; } } } } return(dt); }
public static ModelTypeUploadModel ReadExcel(UploadFileImportModel model) { ModelTypeUploadModel modelTypeUpload = new ModelTypeUploadModel(); List <List <string> > rowValues = new List <List <string> >(); List <ModelTypeTempSheetModel> sheetModels = new List <ModelTypeTempSheetModel>(); ModelTypeTempSheetModel sheetModel; using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(model.FileName, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; int sheetCount = 0; foreach (Sheet sheet in workbookPart.Workbook.Sheets) { sheetModel = new ModelTypeTempSheetModel(); WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id)); Worksheet worksheet = workbookPart.WorksheetParts.First().Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); List <Row> rows = sheetData.Descendants <Row>().ToList(); List <string> preRow = new List <string>(); List <string> cellValues = new List <string>(); int indexMainEquipStart = 0; int indexPNoStart = 0; int indexTypeStart = 0; int indexVinStart = 0; int indexEngineSerialNoStart = 0; int indexErrorDescriptionStart = 0; int inedexRowOfHeader_Start = 6; int indexRowOfHeader_End = 8; // Assign header value foreach (Cell cell in rows.ElementAt(5).Descendants <Cell>()) { if (new[] { "A6", "B6", "C6", "E6", "F6" }.Contains(cell.CellReference.Value)) { string value = GetCellValue(workbookPart, sheet, cell.CellReference); switch (cell.CellReference.Value) { case "A6": sheetModel.YM = value; break; case "B6": sheetModel.Model = value; break; case "C6": sheetModel.Door = value; break; case "E6": sheetModel.Plant = value; break; case "F6": sheetModel.Status = value; break; } } } // Find column header index for (var i = inedexRowOfHeader_Start; i < indexRowOfHeader_End; i++) { foreach (Cell cell in rows.ElementAt(i).Descendants <Cell>()) { if (GetCellValue(workbookPart, sheet, cell.CellReference) == "MAIN EQUIPMENT") { indexMainEquipStart = GetColumnIndex(cell.CellReference); } if (GetCellValue(workbookPart, sheet, cell.CellReference) == "P.No.") { indexPNoStart = GetColumnIndex(cell.CellReference); } if (GetCellValue(workbookPart, sheet, cell.CellReference) == "TYPE") { indexTypeStart = GetColumnIndex(cell.CellReference); } if (GetCellValue(workbookPart, sheet, cell.CellReference) == "VIN") { indexVinStart = GetColumnIndex(cell.CellReference); } if (GetCellValue(workbookPart, sheet, cell.CellReference) == "ENGINE SERIAL No.") { indexEngineSerialNoStart = GetColumnIndex(cell.CellReference); } if (GetCellValue(workbookPart, sheet, cell.CellReference) == "Error Description") { indexErrorDescriptionStart = GetColumnIndex(cell.CellReference); } } } ModelTypeTempRowModel modelTypeTempRowModel; ModelTypeTempEngineModel engineModel; List <ModelTypeTempEquipmentModel> equipmentModels; ModelTypeTempEquipmentModel equipmentModel; List <ModelTypeTempTypeModel> typeModels; ModelTypeTempTypeModel typeModel; for (var i = 9; i < rows.Count; i++) { modelTypeTempRowModel = new ModelTypeTempRowModel(); equipmentModels = new List <ModelTypeTempEquipmentModel>(); typeModel = new ModelTypeTempTypeModel(); typeModels = new List <ModelTypeTempTypeModel>(); cellValues = new List <string>(); engineModel = new ModelTypeTempEngineModel(); modelTypeTempRowModel.RowNo = i + 1; foreach (Cell cell in rows.ElementAt(i).Cast <Cell>()) { string currentColumn = GetColumnName(cell.CellReference); int currentIndex = GetColumnIndex(cell.CellReference); string currentCellValue = GetCellValue(workbookPart, sheet, cell.CellReference); int sequence = 1; #region Engine if (new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" }.Contains(GetColumnName(cell.CellReference))) { #region Replace Value if (cell.CellReference == "A" + (i + 1)) { if (string.IsNullOrEmpty(currentCellValue)) { currentCellValue = preRow[0]; } } if (cell.CellReference == "B" + (i + 1)) { if (string.IsNullOrEmpty(currentCellValue)) { currentCellValue = preRow[1]; } } if (cell.CellReference == "C" + (i + 1)) { if (string.IsNullOrEmpty(currentCellValue)) { currentCellValue = preRow[2]; } } if (cell.CellReference == "D" + (i + 1)) { if (string.IsNullOrEmpty(currentCellValue)) { currentCellValue = preRow[3]; } } if (cell.CellReference == "E" + (i + 1)) { if (string.IsNullOrEmpty(currentCellValue)) { currentCellValue = preRow[4]; } } #endregion switch (GetColumnName(cell.CellReference)) { case "A": engineModel.SS = currentCellValue; break; case "B": engineModel.DISP = currentCellValue; break; case "C": engineModel.COMCARB = currentCellValue; break; case "D": engineModel.Grade = currentCellValue; break; case "E": engineModel.Mis = currentCellValue; break; case "F": engineModel.ModelCode01 = currentCellValue; break; case "G": engineModel.ModelCode02 = currentCellValue; break; case "H": engineModel.ModelCode03 = currentCellValue; break; case "I": engineModel.ModelCode04 = currentCellValue; break; case "J": engineModel.ModelCode05 = currentCellValue; break; } } #endregion #region MAIN EQUIPMENT string columnEndGetEquipment = GetColumnName(GetEndColumnMergeCell(workbookPart, sheet, "K7")); int indexMainEquipEnd = GetColumnIndex(columnEndGetEquipment); if (currentIndex >= indexMainEquipStart && currentIndex <= indexMainEquipEnd) // Start K Column { equipmentModel = new ModelTypeTempEquipmentModel { EquipmentName = GetCellValue(workbookPart, sheet, currentColumn + 9), EquipmentValue = currentCellValue, Sequence = sequence }; sequence++; equipmentModels.Add(equipmentModel); } #endregion #region PNo if (currentIndex == indexPNoStart) { modelTypeTempRowModel.PNo = currentCellValue; } #endregion #region TYPE if (currentIndex >= indexTypeStart && currentIndex <= indexVinStart - 1) { typeModel = new ModelTypeTempTypeModel { ModelType = GetCellValue(workbookPart, sheet, currentColumn + 9), ModelCode = currentCellValue, Sequence = sequence }; typeModels.Add(typeModel); } #endregion #region VIN if (currentIndex == indexVinStart) { modelTypeTempRowModel.VIN = currentCellValue; } #endregion cellValues.Add(currentCellValue); } // End Cell preRow = cellValues; rowValues.Add(cellValues); modelTypeTempRowModel.ModelTypeTempEngines.Add(engineModel); modelTypeTempRowModel.ModelTypeTempEquipmentModels.AddRange(equipmentModels); modelTypeTempRowModel.ModelTypeTempTypeModels.AddRange(typeModels); sheetModel.ModelTypeTempRowModels.Add(modelTypeTempRowModel); } //End Row sheetModel.SheetNo = ++sheetCount; modelTypeUpload.ModelTypeTempSheetModels.Add(sheetModel); } } modelTypeUpload.UploadStatusID = 44; // wait edit return(modelTypeUpload); }
// Retrieving public static DataTable GetWorkSheetData(SpreadsheetDocument ss, String sheetName, bool includeHeaderRow, int headerRowOffset = 0) { DataTable dt = new DataTable(); WorksheetPart wsp; if (sheetName != null) { wsp = GetWorkSheetPartByName(ss, sheetName); } else { wsp = GetFirstWorkSheetPart(ss); } if (wsp != null) { Worksheet ws = wsp.Worksheet; SheetData sheet_data = ws.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheet_data.Descendants <Row>(); foreach (Cell cell in rows.ElementAt(0)) { String ColumnName = GetCellValue(ss, cell); if (!dt.Columns.Contains(ColumnName)) { dt.Columns.Add(ColumnName); } } // Fill data table with Excel data. // This implementation includes blank cells from Excel datasets, meaning the datatable will not // skew when encoutering skipped elements in the XML file (blank cells are null, and are not included in the XML). foreach (Row row in rows) { DataRow tempRow = dt.NewRow(); int columnIndex = 0; foreach (Cell cell in row.Descendants <Cell>()) { // Gets the column index of the cell with data int cellColumnIndex = ColumnNames.IndexOf(GetColumnName(cell.CellReference)); // (int)GetColumnIndexFromName(GetColumnName(cell.CellReference)); if (columnIndex < cellColumnIndex) { do { if (tempRow.ItemArray.Length > columnIndex) { tempRow[columnIndex] = String.Empty; } else { break; } columnIndex++; }while (columnIndex < cellColumnIndex); } String cell_value = GetCellValue(ss, cell); if (columnIndex < tempRow.ItemArray.Length) { tempRow[columnIndex] = cell_value; } columnIndex++; } dt.Rows.Add(tempRow); } if (headerRowOffset != 0) { for (int row = 1; row < (headerRowOffset + 1); row++) { dt.Rows.RemoveAt(0); } // Attempt to set new DataTable header names (as the headers would have been set earlier, without regard for the header offset) if (dt.Rows.Count > 0) { for (int j = 0; j < dt.Columns.Count; j++) { String ColumnName = dt.Rows[0][j].ToString(); if (String.IsNullOrEmpty(ColumnName)) // give dummny name if necessary { ColumnName = j.ToString(); } dt.Columns[j].ColumnName = ColumnName; } } } if (!includeHeaderRow) { dt.Rows.RemoveAt(0); } } return(dt); }
public static List <SetupExcelModel> GetDataTableFromSpreadsheet(Stream MyExcelStream, bool ReadOnly, BHSingleSetupModel bHSingleSetupModel, HRProfile hRProfile) { List <SetupExcelModel> dt = new List <SetupExcelModel>(); using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(MyExcelStream, ReadOnly)) { WorkbookPart workbookPart = sDoc.WorkbookPart; IEnumerable <Sheet> sheets = sDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)sDoc.WorkbookPart.GetPartById(relationshipId); Worksheet workSheet = worksheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); foreach (Cell cell in rows.ElementAt(0)) { //dt.Add(GetCellValue(sDoc, cell)); } SetupExcelModel setupExcelModel; Debug.WriteLine("rows length = " + rows.Count()); foreach (Row row in rows) { setupExcelModel = new SetupExcelModel(); setupExcelModel.Id = (int)row.RowIndex.Value; for (int i = 0; i < row.Descendants <Cell>().Count(); i++) { Debug.WriteLine("i = " + i); switch (i) { case 0: //StaffBranch setupExcelModel.StaffBranch = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 1: //StaffBranchCode setupExcelModel.StaffBranchCode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 2: //StaffNumber setupExcelModel.StaffNumber = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 3: //StaffName setupExcelModel.StaffName = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); break; case 4: //StaffRole--SelectedAppraisalPeriod--SetupAppPeriod--HRProfile--Comments setupExcelModel.StaffRole = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)); setupExcelModel.SelectedAppraisalPeriod = bHSingleSetupModel.SelectedAppraisalPeriod; setupExcelModel.SetupAppPeriod = bHSingleSetupModel.SetupAppPeriod; setupExcelModel.HRProfile = hRProfile; setupExcelModel.Comments = bHSingleSetupModel.Comments; dt.Add(setupExcelModel); break; } } } } return(dt); }
/// <summary> /// Read Data from selected excel file into DataTable /// </summary> /// <param name="filename">Excel File Path</param> /// <returns></returns> /// private DataTable ReadExcelFile(string filename, string tablename, string[] fieldstotrim) { // Initialize an instance of DataTable DataTable dt = new DataTable(); dt.TableName = tablename; int rowcounter = 0; try { // Use SpreadSheetDocument class of Open XML SDK to open excel file using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false)) { // Get Workbook Part of Spread Sheet Document WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; // Get all sheets in spread sheet document IEnumerable <Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); // Get relationship Id string relationshipId = sheetcollection.First().Id.Value; // Get sheet1 Part of Spread Sheet Document WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId); // Get Data in Excel file SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); IEnumerable <Row> rowcollection = sheetData.Descendants <Row>(); if (rowcollection.Count() == 0) { return(dt); } // Add columns foreach (Cell cell in rowcollection.ElementAt(0)) { dt.Columns.Add(GetValueOfCell(spreadsheetDocument, cell)); } // Add rows into DataTable foreach (Row row in rowcollection) { DataRow temprow = dt.NewRow(); int columnIndex = 0; string columnname = ""; foreach (Cell cell in row.Descendants <Cell>()) { // Get Cell Column Index columnname = GetColumnName(cell.CellReference); int cellColumnIndex = GetColumnIndex(columnname); if (columnIndex < cellColumnIndex) { do { try { temprow[columnIndex] = string.Empty; columnIndex++; } catch (Exception x) { System.Diagnostics.Debug.Print(x.Message); } }while (columnIndex < cellColumnIndex); } if (fieldstotrim != null && fieldstotrim.Where(f => f.Equals(columnname)).Count() == 1) { temprow[columnIndex] = GetValueOfCell(spreadsheetDocument, cell).Replace(" ", ""); } else { temprow[columnIndex] = GetValueOfCell(spreadsheetDocument, cell); } columnIndex++; if (columnIndex >= 7) { break; } } // Add the row to DataTable // the rows include header row dt.Rows.Add(temprow); rowcounter++; } } // Here remove header row dt.Rows.RemoveAt(0); return(dt); } catch (IOException ex) { throw new IOException(ex.Message); } }
/// <summary> /// Gets work sheet rows. /// </summary> /// <param name="worksheet">The work sheet.</param> /// <returns>The work sheet rows.</returns> internal IEnumerable <Row> GetWorkSheetRows(Worksheet worksheet) { SheetData sheetData = worksheet.GetFirstChild <SheetData>(); return(sheetData?.Descendants <Row>()); }
private static void ReadExcelData(ClientContext clientContext, string fileName) { bool IsError = true; string strErrorMsg = string.Empty; const string lstDocName = "Documents"; try { DataTable dataTable = new DataTable("ExcelDataTable"); List list = clientContext.Web.Lists.GetByTitle(lstDocName); clientContext.Load(list.RootFolder); clientContext.ExecuteQuery(); string fileServerRelativeUrl = list.RootFolder.ServerRelativeUrl + "/" + "ExcelFile.xlsx"; SP.File file = clientContext.Web.GetFileByServerRelativeUrl(fileServerRelativeUrl); ClientResult <System.IO.Stream> data = file.OpenBinaryStream(); clientContext.Load(file); clientContext.ExecuteQuery(); using (System.IO.MemoryStream mStream = new System.IO.MemoryStream()) { if (data != null) { data.Value.CopyTo(mStream); using (SpreadsheetDocument documnet = SpreadsheetDocument.Open(mStream, false)) { WorkbookPart workbookpart = documnet.WorkbookPart; IEnumerable <Sheet> sheets = documnet.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)documnet.WorkbookPart.GetPartById(relationshipId); Worksheet workSheet = worksheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); foreach (Cell cell in rows.ElementAt(0)) { string str = GetCellValue(clientContext, documnet, cell); dataTable.Columns.Add(str); } foreach (Row row in rows) { if (row != null) { DataRow dataRow = dataTable.NewRow(); for (int i = 0; i < row.Descendants <Cell>().Count(); i++) { dataRow[i] = GetCellValue(clientContext, documnet, row.Descendants <Cell>().ElementAt(i)); } dataTable.Rows.Add(dataRow); } } dataTable.Rows.RemoveAt(0); } } } UpdateSPList(clientContext, dataTable, fileName); IsError = false; } catch (Exception e) { IsError = true; Console.WriteLine(e.Message); Console.WriteLine("second catch block"); } finally { if (IsError) { //Logging } } }
/// <summary> /// Adjust the column width to its best fit /// </summary> /// <param name="document">SpreadSheetDocument reference</param> public static void AutoAdjustWidth(this SpreadsheetDocument document) { if (document.IsNotValid()) { return; } //Maximum digit with per cell ~96dpi (most common display) double max = 8; //Add the columns for each worksheet that has any valid sheet data document.WorkbookPart.WorksheetParts .Select(wsp => wsp.Worksheet) .Where(wsp => wsp.Descendants <SheetData>().Any(sd => sd.Descendants <Row>().Any())) .ToList() .ForEach(ws => { //Current SheetData reference SheetData sd = ws.GetFirstChild <SheetData>(); //Find the widest row Row row = sd.Descendants <Row>().First(); //Array of lengths (max length per cell per row) int[] lengths = new int[row.Descendants <Cell>().Count()]; for (int i = 0; i < lengths.Length; i++) { lengths[i] = 0; } //Find the widest cell in all rows foreach (Row r in sd.Descendants <Row>()) { for (int i = 0; i < lengths.Length; i++) { Cell cell = r.Descendants <Cell>().ElementAt(i); int length; //Take the cell length value from the shared string table if the cell has its value in the shared string table if (cell.DataType.Equals(CellValues.SharedString) && cell.CellValue.Text.IsNumber()) { int index = Convert.ToInt32(cell.CellValue.Text); length = StringPart.SharedStringTable.ElementAt(index).InnerText.Length; } else { //Take the length from the cell value directly length = cell.CellValue.Text.Length; } //Set the current length for the cell at row if the length at the current position is less than the current if (lengths[i] < length) { lengths[i] = length; } } } //Get or create the custom columns reference Columns columns = ws.GetFirstChild <Columns>() ?? new Columns(); //Adjust the columns width for (uint i = 1; i <= lengths.Length; i++) { double width = ((lengths[i - 1] * max + 5) / max * 256) / 256; columns.Append(new Column() { Min = i, Max = i, Width = width + 1, CustomWidth = true }); } //Add the columns if they do not exists if (ws.GetFirstChild <Columns>() == null) { ws.InsertBefore(columns, sd); } //Save all changes made ws.Save(); }); document.Save(); }
private static List<ColumnData> GetColumnsEquivalences(this SpreadsheetDocument document, SheetData sheetData, ResultTable results) { var resultsCols = results.Columns.ToDictionary(c => c.Column.DisplayName); var headerCells = sheetData.Descendants<Row>().FirstEx().Descendants<Cell>().ToList(); var templateCols = headerCells.ToDictionary(c => document.GetCellValue(c)); var rowDataCellTemplates = sheetData.Descendants<Row>() .FirstEx(r => IsValidRowDataTemplate(r, headerCells)) .Descendants<Cell>().ToList(); var dic = templateCols.OuterJoinDictionaryCC(resultsCols, (name, cell, resultCol) => { if (resultCol == null) throw new ApplicationException(ExcelMessage.TheExcelTemplateHasAColumn0NotPresentInTheFindWindow.NiceToString().FormatWith(name)); if (cell != null) { return new ColumnData { IsNew = false, StyleIndex = rowDataCellTemplates[headerCells.IndexOf(cell)].StyleIndex, Column = resultCol, }; } else { CellBuilder cb = PlainExcelGenerator.CellBuilder; return new ColumnData { IsNew = true, StyleIndex = 0, Column = resultCol, }; } }); return dic.Values.ToList(); }
public void openWorkBook(string fileName, DataSet studentData) { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, true)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; IEnumerable <Sheet> sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = InsertErrorWorksheet(workbookPart); Worksheet workSheet = worksheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); List <string> columnNames = new List <string>(); //Row headerRow = rows.ElementAt(0); // int errorColIndex = headerRow.Count(); WorkbookStylesPart styles = workbookPart.WorkbookStylesPart; //Write Dataset var dataTable = studentData.Tables[0]; //var errorStyle = SetErrorStyle(styles.Stylesheet); //// Set columns //foreach (Cell cell in headerRow) //{ // columnNames.Add(getCellValue(workbookPart, cell)); // //Console.Write($"{} "); //} if (dataTable.Columns.Contains("Error")) { var columnList = new List <string> { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L" }; //add header column var headerRow = AddRowToErrorSheet(sheetData); foreach (DataColumn column in dataTable.Columns) { var cellReference = columnList[column.Ordinal] + headerRow.RowIndex; // If there is not a cell with the specified column name, insert one. // Cells must be in sequential order according to CellReference. Determine where to insert the new cell. Cell refCell = null; foreach (Cell cell in headerRow.Elements <Cell>()) { if (cell.CellReference.Value.Length == cellReference.Length) { if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) { refCell = cell; break; } } } var newRowCell = AddRowText(workbookPart, headerRow, column.ColumnName); newRowCell.CellReference = cellReference; headerRow.InsertBefore(newRowCell, refCell); } foreach (DataRow errorRow in dataTable.Rows) { var row = AddRowToErrorSheet(sheetData); foreach (DataColumn column in dataTable.Columns) { var cellReference = columnList[column.Ordinal] + row.RowIndex; // If there is not a cell with the specified column name, insert one. // Cells must be in sequential order according to CellReference. Determine where to insert the new cell. Cell refCell = null; foreach (Cell cell in row.Elements <Cell>()) { if (cell.CellReference.Value.Length == cellReference.Length) { if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) { refCell = cell; break; } } } var newRowCell = AddRowText(workbookPart, row, errorRow[column.ColumnName].ToString()); newRowCell.CellReference = cellReference; row.InsertBefore(newRowCell, refCell); } } } Console.WriteLine(); //Write data to datatable //var indexDiff = 2; //var dataTable = studentData.Tables[0]; //foreach (Row row in rows.Skip(1)) //{ // var rowIndex = Convert.ToInt32(row.RowIndex.ToString()) - indexDiff; // var errorText= dataTable.Rows[rowIndex].Field<string>("Error"); // var newRowCell = AddRowText(workbookPart, row, errorText); // // newRowCell.StyleIndex = errorStyle; // if (row.Count() < errorColIndex) // { // Console.WriteLine($"Add cells between { row.Count() } to {errorColIndex}"); // for(int x = row.Count(); x <= errorColIndex; x++) // { // Cell emptyCell = new Cell() { CellReference = row.RowIndex.ToString(), CellValue = new CellValue()}; // row.InsertAt(emptyCell, x); // } // } // if (row.ElementAt(errorColIndex) != null) // { // var existingCell = row.Descendants<Cell>().ElementAt(errorColIndex); // existingCell.CellValue.Text = newRowCell.CellValue.InnerText; // existingCell.DataType = newRowCell.DataType; // // existingCell.StyleIndex = errorStyle; // } // else // { // row.InsertAt(newRowCell, errorColIndex); // } // Console.WriteLine(row.Count()); //} workSheet.Save(); } }
static void InsertIntoDataTable(ClientContext clientContext, String fileName) //this method is used to read the { //data from excel then stored on datatble string strErrorMsg = string.Empty; try { DataTable dataTable = new DataTable("ExcelInformation");//Use temporary datatble List list = clientContext.Web.Lists.GetByTitle(Constant.RootFolder); clientContext.Load(list.RootFolder); clientContext.ExecuteQuery(); string fileServerRelativeUrl = list.RootFolder.ServerRelativeUrl + "/" + fileName; File file = clientContext.Web.GetFileByServerRelativeUrl(fileServerRelativeUrl);//Extracting the file ClientResult <System.IO.Stream> data = file.OpenBinaryStream(); clientContext.Load(file); clientContext.ExecuteQuery(); using (System.IO.MemoryStream mStream = new System.IO.MemoryStream()) { if (data != null) { data.Value.CopyTo(mStream); using (SpreadsheetDocument document = SpreadsheetDocument.Open(mStream, false)) { WorkbookPart workbookPart = document.WorkbookPart; IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId); Worksheet workSheet = worksheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); foreach (Cell cell in rows.ElementAt(0)) //reading the file colums { string str = GetCellValue(document, cell); //getting cell data using the cell value(column name) dataTable.Columns.Add(str); //after adding to the datatable } foreach (Row row in rows) //rows contains the all information now take each one from roes then added to the datatable { if (row != null) //if row does't contains the data then no need to insert the data { DataRow dataRow = dataTable.NewRow(); for (int i = 0; i < row.Descendants <Cell>().Count(); i++) { dataRow[i] = GetCellValue(document, row.Descendants <Cell>().ElementAt(i)); //here reading the entire row from excel sheet } dataTable.Rows.Add(dataRow); //finally addin the each row to the datatble } } dataTable.Rows.RemoveAt(0); //deleting the column heading } } } OriginaldataTable = dataTable.Copy(); //copied to temporary datatble to original datatable // display(); UploadFile(clientContext, dataTable, fileName); //Here Upload the all files to sharepoint string FileNameAfterChange = ExportToExcelSheet(); UploadExcelFileToSharepoint(clientContext, FileNameAfterChange + ".xlsx"); //Upload the updated sharepoint } catch (Exception e) { LogClass.RecordException(e); strErrorMsg = e.Message; } }
public static Cell FindCell(this SheetData sheetData, string addressName) { return(sheetData.Descendants <Cell>(). Where(c => c.CellReference == addressName).FirstOrDefault()); }
private IEnumerable<Cell> GetColumnCells(SheetData sheetData, int colIndex) { var cells = sheetData.Descendants<Row>() .Select(x => x.Descendants<Cell>().ElementAt(colIndex)); return cells; }
/// <summary> /// Read Data from selected excel file into DataTable /// </summary> /// <param name="filename">Excel File Path</param> /// <returns></returns> private DataTable ReadExcelFile(string filename) { // Initialize an instance of DataTable DataTable dt = new DataTable(); try { // Use SpreadSheetDocument class of Open XML SDK to open excel file using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false)) { // Get Workbook Part of Spread Sheet Document WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; // Get all sheets in spread sheet document IEnumerable <Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); // Get relationship Id string relationshipId = sheetcollection.First().Id.Value; // Get sheet1 Part of Spread Sheet Document WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId); // Get Data in Excel file SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); IEnumerable <Row> rowcollection = sheetData.Descendants <Row>(); if (rowcollection.Count() == 0) { return(dt); } // Add columns foreach (Cell cell in rowcollection.ElementAt(0)) { dt.Columns.Add(GetValueOfCell(spreadsheetDocument, cell)); } // Add rows into DataTable foreach (Row row in rowcollection) { DataRow temprow = dt.NewRow(); int columnIndex = 0; foreach (Cell cell in row.Descendants <Cell>()) { // Get Cell Column Index int cellColumnIndex = GetColumnIndex(GetColumnName(cell.CellReference)); if (columnIndex < cellColumnIndex) { do { temprow[columnIndex] = string.Empty; columnIndex++; }while (columnIndex < cellColumnIndex); } temprow[columnIndex] = GetValueOfCell(spreadsheetDocument, cell); columnIndex++; } // Add the row to DataTable // the rows include header row dt.Rows.Add(temprow); } } // Here remove header row dt.Rows.RemoveAt(0); return(dt); } catch (IOException ex) { throw new IOException(ex.Message); } }
private static bool LoadRowsFromExcel(this DataTable dt, SpreadsheetDocument sd, LoadRowsFromSpreadsheetSettings settings) { DataTableHelpers.RequiresZeroRows(dt, nameof(dt)); Requires.NonNull(sd, nameof(sd)); Requires.NonNull(settings, nameof(settings)); var rows = new List <IList <object> >(); var sharedStringDictionary = ConvertSharedStringTableToDictionary(sd); var sheets = sd.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); int sheetNumber = 0; foreach (var sheet in sheets) { if (sheetNumber == settings.SheetNumber || 0 == string.Compare(settings.SheetName, sheet.Name, true)) { if (settings.UseSheetNameForTableName) { dt.TableName = sheet.Name; } string relationshipId = sheet.Id.Value; var worksheetPart = (WorksheetPart)sd.WorkbookPart.GetPartById(relationshipId); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); IEnumerable <Row> eRows = sheetData.Descendants <Row>(); foreach (Row erow in eRows) { CreateRow: var row = new List <object>(); rows.Add(row); foreach (var cell in erow.Descendants <Cell>()) { var cr = GetColRowFromCellReference(cell.CellReference); if (rows.Count <= cr.Item2) { goto CreateRow; } while (row.Count < cr.Item1) { row.Add(null); } Debug.Assert(row.Count == cr.Item1); var val = GetCellValue(sd, cell, settings.TreatAllValuesAsText, sharedStringDictionary); row.Add(val); } } GC.Collect(); IEnumerable <IList <object> > positionnedRows; if (settings.SkipRawRows.HasValue) { positionnedRows = rows.Skip(settings.SkipRawRows.Value); } else if (settings.SkipWhileTester != null) { positionnedRows = rows.SkipWhile(settings.SkipWhileTester); } else { positionnedRows = rows; } dt.LoadRows(positionnedRows, settings); return(true); } ++sheetNumber; } if (settings.ThrowOnMissingSheet) { throw new Exception(string.Format( "Sheet [{0}] was not found", (object)settings.SheetNumber ?? (object)settings.SheetName)); } return(false); }
public static Cell GetCell(SheetData sheetData, string cellAddress) { uint rowIndex = uint.Parse(Regex.Match(cellAddress, @"[0-9]+").Value); return(sheetData.Descendants <Row>().FirstOrDefault(p => p.RowIndex == rowIndex).Descendants <Cell>().FirstOrDefault(p => p.CellReference == cellAddress)); }
public async Task <FileInfo> GetExcelFile() { Dictionary <string, Int32> sharedStringDic = new Dictionary <string, int>(); const string excelTemplate = "Tools_CalDue_Template.xlsx"; const string sheetname = "Sheet1"; const int styleString = 0; // for this template const int styleDate = 3; // for this template string providername = Startup.AppSettings["StorageProvider"]; string folder = Startup.AppSettings["PdfFoldername"]; FileInfo fileinfo; Int32 sharedStringId = 0; UInt32 lineIndex; SysIO.MemoryStream ms = new SysIO.MemoryStream();; if (providername == "localfile") { string filepath = SysIO.Path.Combine(folder, excelTemplate); //fileinfo.FileByteStream = SysIO.File.Open(filepath, SysIO.FileMode.Open); SysIO.FileStream fs; fs = SysIO.File.Open(filepath, System.IO.FileMode.Open); fs.CopyTo(ms); long bytesInStream = ms.Length; fs.Close(); } else if (providername == "AzureBlob") { string connectionstring = Startup.AppSettings["AzureBlob"]; CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionstring); CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient(); CloudBlobContainer container = blobClient.GetContainerReference("templates"); CloudBlockBlob blockBlob = container.GetBlockBlobReference(excelTemplate); SysIO.Stream frs = await blockBlob.OpenReadAsync(); frs.CopyTo(ms); long bytesInStream = ms.Length; bool canread = ms.CanRead; bool canwrite = ms.CanWrite; frs.Close(); } SpreadsheetDocument document = SpreadsheetDocument.Open(ms, true); WorkbookPart wbPart = document.WorkbookPart; Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetname).FirstOrDefault(); if (theSheet == null) { throw new ArgumentException("sheetName"); } WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Worksheet ws = wsPart.Worksheet; Columns columns = ws.Descendants <Columns>().FirstOrDefault(); SheetData sheetData = ws.Descendants <SheetData>().FirstOrDefault(); Row firstRow = sheetData.Descendants <Row>().ElementAt(0); // get first row , line 1 firstRow.DyDescent = 0.3D; Row secondRow = sheetData.Descendants <Row>().ElementAt(1); // get second row , line 2 secondRow.Remove(); SharedStringTablePart sharedStringPart = wbPart.GetPartsOfType <SharedStringTablePart>().First(); SharedStringTable sharedStringTable = sharedStringPart.SharedStringTable; foreach (SharedStringItem item in sharedStringTable.Elements <SharedStringItem>()) // read shared string and add to Dictionary { if (item.InnerText != null) { sharedStringDic.Add(item.InnerText, sharedStringId); ++sharedStringId; } } lineIndex = 2; string id; // StoreLocation が column "B" と "M" の 2箇所にある foreach (var tool in filteredData) { Row newRow = new Row() { RowIndex = lineIndex, Spans = new ListValue <StringValue>() { InnerText = "1:20" }, Height = 16.5D, CustomHeight = true, DyDescent = 0.3D }; Cell cell1 = new Cell() { CellReference = "A" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Plant).ToString(); cell1.CellValue = new CellValue(id); Cell cell2 = new Cell() { CellReference = "B" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.StoreLocation).ToString(); cell2.CellValue = new CellValue(id); Cell cell3 = new Cell() { CellReference = "C" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.ToolkitSloc).ToString(); cell3.CellValue = new CellValue(id); Cell cell4 = new Cell() { CellReference = "D" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.SerialNumber).ToString(); cell4.CellValue = new CellValue(id); Cell cell5 = new Cell() { CellReference = "E" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Material).ToString(); cell5.CellValue = new CellValue(id); Cell cell6 = new Cell() { CellReference = "F" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Description).ToString(); cell6.CellValue = new CellValue(id); Cell cell7 = new Cell() { CellReference = "G" + lineIndex.ToString(), StyleIndex = styleDate }; if (tool.LatestCalDate != null) { cell7.CellValue = new CellValue(((DateTime)tool.LatestCalDate).ToOADate().ToString()); } else { cell7.CellValue = new CellValue(); } Cell cell8 = new Cell() { CellReference = "H" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.CalStatus).ToString(); cell8.CellValue = new CellValue(id); Cell cell9 = new Cell() { CellReference = "I" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Comment).ToString(); cell9.CellValue = new CellValue(id); Cell cell10 = new Cell() { CellReference = "J" + lineIndex.ToString(), StyleIndex = styleDate }; if (tool.CalDue != null) { cell10.CellValue = new CellValue(((DateTime)tool.CalDue).ToOADate().ToString()); } else { cell10.CellValue = new CellValue(); } Cell cell11 = new Cell() { CellReference = "K" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.CalPlace).ToString(); cell11.CellValue = new CellValue(id); Cell cell12 = new Cell() { CellReference = "L" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString }; cell12.CellValue = new CellValue(tool.CalInterval.ToString()); Cell cell13 = new Cell() { CellReference = "M" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.StoreLocation).ToString(); cell13.CellValue = new CellValue(id); Cell cell14 = new Cell() { CellReference = "N" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.SystemStatus).ToString(); cell14.CellValue = new CellValue(id); // todo tool.System_status is null, replace field to represent value Cell cell15 = new Cell() { CellReference = "O" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.UserStatus).ToString(); cell15.CellValue = new CellValue(id); Cell cell16 = new Cell() { CellReference = "P" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Room).ToString(); cell16.CellValue = new CellValue(id); Cell cell17 = new Cell() { CellReference = "Q" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.SuperordEquip).ToString(); cell17.CellValue = new CellValue(id); Cell cell18 = new Cell() { CellReference = "R" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.SortField).ToString(); cell18.CellValue = new CellValue(id); Cell cell19 = new Cell() { CellReference = "S" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Machine).ToString(); cell19.CellValue = new CellValue(id); Cell cell20 = new Cell() { CellReference = "T" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.ToolkitMachine).ToString(); cell20.CellValue = new CellValue(id); newRow.Append(cell1); newRow.Append(cell2); newRow.Append(cell3); newRow.Append(cell4); newRow.Append(cell5); newRow.Append(cell6); newRow.Append(cell7); newRow.Append(cell8); newRow.Append(cell9); newRow.Append(cell10); newRow.Append(cell11); newRow.Append(cell12); newRow.Append(cell13); newRow.Append(cell14); newRow.Append(cell15); newRow.Append(cell16); newRow.Append(cell17); newRow.Append(cell18); newRow.Append(cell19); newRow.Append(cell20); sheetData.AppendChild <Row>(newRow); ++lineIndex; } Int32 count = 0; foreach (string key in sharedStringDic.Keys) { if (count >= sharedStringId) { sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(key))); } ++count; } sharedStringTable.Save(); ws.Save(); wbPart.Workbook.Save(); document.Close(); fileinfo = new FileInfo(); fileinfo.FileName = ""; // file name is added at client (Silverlight) fileinfo.Length = ms.Length; fileinfo.byteArray = new byte[fileinfo.Length + 10]; Array.Copy(ms.GetBuffer(), fileinfo.byteArray, fileinfo.Length); //Array.Resize(ref fileinfo.FileByteStream, (int)ms.Length) ; //fileinfo.Length = ms.Length; return(fileinfo); }
static string XLGetValue(string fileName, string sheetName) { string value = null; var error = string.Empty; try { using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false)) { WorkbookPart wbPart = document.WorkbookPart; Sheet theSheet = wbPart.Workbook.Descendants <Sheet>(). FirstOrDefault(s => s.Name == sheetName); if (theSheet == null) { throw new ArgumentException("sheetName"); } WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Worksheet workSheet = wsPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); var rowss = sheetData.Elements <Row>(); //skip first heading row and check for non-empty rows foreach (var row in rows.Skip(1).Where(d => d.InnerText != "")) { var theRow = rows.FirstOrDefault(r => r.RowIndex.Value == row.RowIndex.Value); //get only 2 (A and B) columns, if require others then take accordingly foreach (Cell cell in theRow.Take(2)) { if (cell.DataType != null) { switch (cell.DataType.Value) { case CellValues.SharedString: var stringTable = wbPart.SharedStringTablePart; if (stringTable != null) { var textItem = stringTable.SharedStringTable. ElementAtOrDefault(int.Parse(cell.InnerText)); if (textItem != null) { if (cell.CellReference.ToString().Contains("A")) { value += "Cell Value = " + textItem.InnerText + " of cell = " + cell.CellReference.ToString(); } } } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } } } } catch (Exception ex) { error += "Error occured<br/>"; } return(value); }
public async Task <FileInfo> GetExcelFile() { Dictionary <string, Int32> sharedStringDic = new Dictionary <string, int>(); const string excelTemplate = "CalInProcessTemplateVer4.xlsx"; const string sheetname = "Sheet1"; uint styleString = 0; uint styleDate = 0; string providername = Startup.AppSettings["StorageProvider"]; FileInfo fileinfo; Int32 sharedStringId = 0; UInt32 lineIndex; SysIO.MemoryStream ms = new SysIO.MemoryStream();; if (providername == "localfile") { // Response.WriteFile(AppResources.GetCalCertPath(pdffilename)); TemplateFolder string folder = Startup.AppSettings["PdfFoldername"]; string filepath = SysIO.Path.Combine(folder, excelTemplate); //fileinfo.FileByteStream = SysIO.File.Open(filepath, SysIO.FileMode.Open); SysIO.FileStream fs; fs = SysIO.File.Open(filepath, System.IO.FileMode.Open); fs.CopyTo(ms); long bytesInStream = ms.Length; fs.Close(); } else if (providername == "AzureBlob") { string connectionstring = Startup.AppSettings["AzureBlob"]; CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionstring); CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient(); CloudBlobContainer container = blobClient.GetContainerReference("templates"); CloudBlockBlob blockBlob = container.GetBlockBlobReference(excelTemplate); //SysIO.Stream templateStream = blockBlob.OpenRead(); //fileinfo.FileByteStream = blockBlob.OpenRead(); SysIO.Stream frs = await blockBlob.OpenReadAsync(); frs.CopyTo(ms); long bytesInStream = ms.Length; bool canread = ms.CanRead; bool canwrite = ms.CanWrite; frs.Close(); } SpreadsheetDocument document = SpreadsheetDocument.Open(ms, true); WorkbookPart wbPart = document.WorkbookPart; Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetname).FirstOrDefault(); if (theSheet == null) { throw new ArgumentException(string.Format("sheetName{0} not found", sheetname)); } WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Worksheet ws = wsPart.Worksheet; Columns columns = ws.Descendants <Columns>().FirstOrDefault(); SheetData sheetData = ws.Descendants <SheetData>().FirstOrDefault(); Row firstRow = sheetData.Descendants <Row>().ElementAt(0); // get first row , line 1 firstRow.DyDescent = 0.3D; Row secondRow = sheetData.Descendants <Row>().ElementAt(1); // get second row , line 2 foreach (Cell cel2nd in secondRow) { if (cel2nd != null) { var cellAdd = cel2nd.CellReference; if (cellAdd == "A2") { styleString = cel2nd.StyleIndex.Value; } if (cellAdd == "H2") { styleDate = cel2nd.StyleIndex.Value; } } } secondRow.Remove(); SharedStringTablePart sharedStringPart = wbPart.GetPartsOfType <SharedStringTablePart>().First(); SharedStringTable sharedStringTable = sharedStringPart.SharedStringTable; foreach (SharedStringItem item in sharedStringTable.Elements <SharedStringItem>()) // read shared string and add to Dictionary { if (item.InnerText != null) { sharedStringDic.Add(item.InnerText, sharedStringId); ++sharedStringId; } } lineIndex = 2; string id; foreach (var entry in filteredData) { Row newRow = new Row() { RowIndex = lineIndex, Spans = new ListValue <StringValue>() { InnerText = "1:22" }, Height = 16.5D, CustomHeight = true, DyDescent = 0.3D }; Cell cell1 = new Cell() { CellReference = "A" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.Plant).ToString(); cell1.CellValue = new CellValue(id); Cell cell2 = new Cell() { CellReference = "B" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.Location).ToString(); cell2.CellValue = new CellValue(id); Cell cell3 = new Cell() { CellReference = "C" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.SerialNumber).ToString(); cell3.CellValue = new CellValue(id); Cell cell4 = new Cell() { CellReference = "D" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.Material).ToString(); cell4.CellValue = new CellValue(id); Cell cell5 = new Cell() { CellReference = "E" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.Description).ToString(); cell5.CellValue = new CellValue(id); Cell cell6 = new Cell() { CellReference = "F" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.CalPlace).ToString(); cell6.CellValue = new CellValue(id); Cell cell7 = new Cell() { CellReference = "G" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString }; cell7.CellValue = new CellValue(entry.CalInterval.ToString()); Cell cell8 = new Cell() { CellReference = "H" + lineIndex.ToString(), StyleIndex = styleDate }; cell8.CellValue = ConvertDateToCellValue(entry.RegisteredDate); Cell cell9 = new Cell() { CellReference = "I" + lineIndex.ToString(), StyleIndex = styleDate }; cell9.CellValue = ConvertDateToCellValue(entry.UserShipDate); Cell cell10 = new Cell() { CellReference = "J" + lineIndex.ToString(), StyleIndex = styleDate }; cell10.CellValue = ConvertDateToCellValue(entry.VenReceiveDate); Cell cell11 = new Cell() { CellReference = "K" + lineIndex.ToString(), StyleIndex = styleDate }; cell11.CellValue = ConvertDateToCellValue(entry.CalDate); Cell cell12 = new Cell() { CellReference = "L" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; string result = ""; if (entry.CalResult == true) { result = "GD"; } if (entry.CalResult == false) { result = "NG"; } id = sharedStringDic.AddToSharedString(result).ToString(); cell12.CellValue = new CellValue(id); Cell cell13 = new Cell() { CellReference = "M" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.VenComment).ToString(); cell13.CellValue = new CellValue(id); Cell cell14 = new Cell() { CellReference = "N" + lineIndex.ToString(), StyleIndex = styleDate }; cell14.CellValue = ConvertDateToCellValue(entry.PlanedShipDate); Cell cell15 = new Cell() { CellReference = "O" + lineIndex.ToString(), StyleIndex = styleDate }; cell15.CellValue = ConvertDateToCellValue(entry.VenShipDate); // todo tool.System_status is null, replace field to represent value Cell cell16 = new Cell() { CellReference = "P" + lineIndex.ToString(), StyleIndex = styleDate }; cell16.CellValue = ConvertDateToCellValue(entry.UserReceiveDate); Cell cell17 = new Cell() { CellReference = "Q" + lineIndex.ToString(), StyleIndex = styleDate }; cell17.CellValue = ConvertDateToCellValue(entry.CcReceiveDate); Cell cell18 = new Cell() { CellReference = "R" + lineIndex.ToString(), StyleIndex = styleDate }; cell18.CellValue = ConvertDateToCellValue(entry.CcUploadDate); Cell cell19 = new Cell() { CellReference = "S" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString }; cell19.CellValue = new CellValue(entry.StdTat.ToString()); Cell cell20 = new Cell() { CellReference = "T" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString }; cell20.CellValue = new CellValue(entry.Tat.ToString()); Cell cell21 = new Cell() { CellReference = "U" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.TatStatus).ToString(); cell21.CellValue = new CellValue(id); Cell cell22 = new Cell() { CellReference = "V" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; string finished = ""; if (entry.Finished == true) { finished = "Done"; } id = sharedStringDic.AddToSharedString(finished).ToString(); cell22.CellValue = new CellValue(id); Cell cell23 = new Cell() { CellReference = "W" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; String yearmonth; if (entry.UserShipDate == null) { yearmonth = ""; } else { yearmonth = String.Format("{0:yyyy-MM}", (DateTime)entry.UserShipDate); } id = sharedStringDic.AddToSharedString(yearmonth).ToString(); cell23.CellValue = new CellValue(id); Cell cell24 = new Cell() { CellReference = "X" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.PMaker).ToString(); cell24.CellValue = new CellValue(id); Cell cell25 = new Cell() { CellReference = "Y" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.PModel).ToString(); cell25.CellValue = new CellValue(id); Cell cell26 = new Cell() { CellReference = "Z" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.PName).ToString(); cell26.CellValue = new CellValue(id); Cell cell27 = new Cell() { CellReference = "AA" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.PSN).ToString(); cell27.CellValue = new CellValue(id); newRow.Append(cell1); newRow.Append(cell2); newRow.Append(cell3); newRow.Append(cell4); newRow.Append(cell5); newRow.Append(cell6); newRow.Append(cell7); newRow.Append(cell8); newRow.Append(cell9); newRow.Append(cell10); newRow.Append(cell11); newRow.Append(cell12); newRow.Append(cell13); newRow.Append(cell14); newRow.Append(cell15); newRow.Append(cell16); newRow.Append(cell17); newRow.Append(cell18); newRow.Append(cell19); newRow.Append(cell20); newRow.Append(cell21); newRow.Append(cell22); newRow.Append(cell23); newRow.Append(cell24); newRow.Append(cell25); newRow.Append(cell26); newRow.Append(cell27); sheetData.AppendChild <Row>(newRow); ++lineIndex; } Int32 count = 0; foreach (string key in sharedStringDic.Keys) { if (count >= sharedStringId) { sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(key))); } ++count; } sharedStringTable.Save(); ws.Save(); wbPart.Workbook.Save(); document.Close(); fileinfo = new FileInfo(); fileinfo.FileName = ""; // file name is added at client (Silverlight) fileinfo.Length = ms.Length; fileinfo.byteArray = new byte[fileinfo.Length + 10]; Array.Copy(ms.GetBuffer(), fileinfo.byteArray, fileinfo.Length); //Array.Resize(ref fileinfo.FileByteStream, (int)ms.Length) ; //fileinfo.Length = ms.Length; return(fileinfo); }